Forum Discussion
Perform Query Steps on Excel file dynamically based on Dynamic Parameter
Hello Anonymous , check out this blog article by PowerPivotPro. This should work for you.
The idea is to find the value in a certain "cell"...and consider that the beginning of the table. In your case it's either A11 or A12. Note that P3's solution only works for 16k rows. This is because the solution "transposes the table" (swaps columns and rows), and Power BI can only support 16k columns.
I wrote a comment midway through that addresses the 16k row limit: search for this text to jump to my comment:
let Source = ,Starting_Value = “Label” // change as needed // these first 3 steps are the same as Mike Ward’s solution ,Add_HeaderCheck_ROW = Table.AddColumn(Source, “Wanted”, each List.Contains(Record.FieldValues(_), Starting_Value)), Skip_RowsAboveHeader_ROW = Table.Skip(Add_HeaderCheck_ROW, each [Wanted] true) ,Remove_HeaderCheck_ROW = Table.RemoveColumns(Skip_RowsAboveHeader_ROW ,{“Wanted”}) // keep only the first row to avoid transpose errors for large tables ,FirstRow_forColumns = Table.FirstN(Remove_HeaderCheck_ROW, 1) ,Transpose = Table.Transpose(FirstRow_forColumns) // these 2 steps are the same as Mike Ward’s solution ,Add_HeaderCheck_COL = Table.AddColumn(Transpose, “Wanted”, each List.Contains(Record.FieldValues(_), Starting_Value)) ,Skip_RowsAboveHeader_COL = Table.Skip(Add_HeaderCheck_COL, each [Wanted] true) // count how many rows the previous step removed…this is how many columns to remove from the un-transposed table ,Rows_Removed = Table.RowCount(Add_HeaderCheck_COL) – Table.RowCount(Skip_RowsAboveHeader_COL) // uses Datachant’s method of removing the first N columns from a table…without referencing column names ,Remove_NColumns = Table.RemoveColumns(Remove_HeaderCheck_ROW ,List.FirstN( Table.ColumnNames(Remove_HeaderCheck_ROW), Rows_Removed) ) ,Headers = Table.PromoteHeaders(Remove_NColumns, [PromoteAllScalars=true]) in HeadersI also converted this M code into a function that you can call for every table. If you had each file as a separate row, you could do a Table.AddColumn() and create the "proper" table. I need to dig up that code. Let me know if you need it.
Cheers,
~ Chris Haas
Hello Anonymous , check out this blog article by PowerPivotPro. This should work for you.
The idea is to find the value in a certain "cell"...and consider that the beginning of the table. In your case it's either A11 or A12. Note that P3's solution only works for 16k rows. This is because the solution "transposes the table" (swaps columns and rows), and Power BI can only support 16k columns.
I wrote a comment midway through that addresses the 16k row limit: search for this text to jump to my comment:
let
Source =
,Starting_Value = “Label” // change as needed
// these first 3 steps are the same as Mike Ward’s solution
,Add_HeaderCheck_ROW = Table.AddColumn(Source, “Wanted”, each
List.Contains(Record.FieldValues(_), Starting_Value)),
Skip_RowsAboveHeader_ROW = Table.Skip(Add_HeaderCheck_ROW, each
[Wanted] true)
,Remove_HeaderCheck_ROW = Table.RemoveColumns(Skip_RowsAboveHeader_ROW
,{“Wanted”})
// keep only the first row to avoid transpose errors for large tables
,FirstRow_forColumns = Table.FirstN(Remove_HeaderCheck_ROW, 1)
,Transpose = Table.Transpose(FirstRow_forColumns)
// these 2 steps are the same as Mike Ward’s solution
,Add_HeaderCheck_COL = Table.AddColumn(Transpose, “Wanted”, each
List.Contains(Record.FieldValues(_), Starting_Value))
,Skip_RowsAboveHeader_COL = Table.Skip(Add_HeaderCheck_COL, each
[Wanted] true)
// count how many rows the previous step removed…this is how many columns to remove from the un-transposed table
,Rows_Removed = Table.RowCount(Add_HeaderCheck_COL) – Table.RowCount(Skip_RowsAboveHeader_COL)
// uses Datachant’s method of removing the first N columns from a table…without referencing column names
,Remove_NColumns = Table.RemoveColumns(Remove_HeaderCheck_ROW
,List.FirstN( Table.ColumnNames(Remove_HeaderCheck_ROW), Rows_Removed) )
,Headers = Table.PromoteHeaders(Remove_NColumns, [PromoteAllScalars=true])
in
Headers
I also converted this M code into a function that you can call for every table. If you had each file as a separate row, you could do a Table.AddColumn() and create the "proper" table. I need to dig up that code. Let me know if you need it.
Cheers,
~ Chris Haas