How do I use 'M' language to only load records before text 'Ending Balance for Period'

[craig811]

Hi ,

When I load my data into Power query or Power BI, I only want to load rows of data for all coulumns before the text '

Ending Balance for Period' found in Column1. Currently I have to use remove rows from the bottom using 'Remove Rows'. The only issue is it's not always the same amount of rows on each sheet. 

[Mariusz]

Hi @craig811 

Can you make the data sample availeple?

Many Thanks

Mariusz 

[BekahLoSurdo · Accepted solution]

Hi @craig811,

Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Threshold", each if [Column1] = "Ending Balance for Period" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Threshold"}, {{"Table", each _}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index1", 0, 1),
    #"Expanded Table" = Table.ExpandTableColumn(#"Added Index", "Table", {"Column1", "Value"}, {"Column1", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table", each ([Index1] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Threshold", "Index1"})
in
    #"Removed Columns"