Forum Discussion
craig811
Helper III
Helper IIIHow do I use 'M' language to only load records before text 'Ending Balance for Period'
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.
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"
2 Replies
BekahLoSurdoResolver IV
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"
