title: Split a table into multiple smaller tables based on a column's value-Power Query
body: <p>I have a table like this that I got using "combine &amp; edit" option in power query that has information from multiple sheets from multiple <code>.xlsx</code> files. Sheetnames never change and they'll stay the same, excel files can change.</p>
  
  <p><a href="https://i.stack.imgur.com/DyMVN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DyMVN.png" alt="enter image description here"></a></p>
  
  <p>Now, I want many tables splitting by <code>column1</code>'s value <code>firstkey</code>. So, I can get multiple tables like this,</p>
  
  <p><a href="https://i.stack.imgur.com/GOYyI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GOYyI.png" alt="enter image description here"></a></p>
  
  <p>I have been Googling to find an answer, still no success. There are threads like <a href="https://community.powerbi.com/t5/Desktop/Splitting-a-table-into-two-based-on-column-value/td-p/115013" rel="nofollow noreferrer">this</a>, that requires you to duplicate the original table and filter each value. </p>
  
  <p>However, in my case, I want to automate in a way if I have new <code>.xlsx</code> files. So, if I get a value <code>Brooklyn Park</code> instead of <code>Bursville</code>, it should be filtered based on <code>Column1</code>'s value. </p>
  
  <p>How can I do this Power Query? </p>
  
  <p><strong>EDIT</strong></p>
  
  <p>As requested, original excel sheet for one file,</p>
  
  <p><a href="https://i.stack.imgur.com/LWpdG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LWpdG.png" alt="enter image description here"></a></p>
  
  <p><code>M</code> code:</p>
  
  <pre><code>let
      Source = Excel_Export,
      #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
      #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
      #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each ([Source.Name] = "Burnsville.xlsx")),
      #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
      #"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
      #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
      #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Address", "Address Number"}, {"Column3", "StreetName"}, {"Column4", "City"}})
  in
      #"Renamed Columns"
  </code></pre>
  
  <p>I used this code to create a function to automate for each file.</p>