Forum Discussion
Dynamically remov columns in power query
Hello, this is the full Function which i have been using in order to reach the wanted behavior :
(table as table, searchText as text) as list =>
let
ColumnNameList = Table.ColumnNames(table),
CleanColumns = List.Transform(ColumnNameList,(x)=> Text.BeforeDelimiter(x,"_")),
ColPositionOne = List.PositionOf(CleanColumns, searchText),
ColPositionTwo = List.PositionOf(List.Range(CleanColumns,ColPositionOne+1),searchText),
ColsToRemove = List.Range(ColumnNameList,ColPositionOne,ColPositionTwo+2),
ResultList = {ColPositionOne,ColPositionTwo}
in
ColsToRemoveI am calling this Function when i want ot delete the columns which are placed BETWEEN the Column named "searchText" - which means in my case this column named "searchText" has to appear twice within my table!
For sure, we could exten the function with searchText1 and searchText2 in order to dynamically delete columns placed between those 2 columns which would be even easier because in this case i would not need to create "CleanColumns" since PowerQuery is automatically renaming the second column named "searchText" into searchText_ColID since two columns must not have the same Header Name.
Finally as i said the function is called by
= Table.RemoveColumns(#"Promoted Headers", fxGetColumnsToRemove(#"Promoted Headers","ColumnName"))
Hope this helps somebody else.
Hi padinator ,
It looks like your [Col1] column holds your data, and the other columns are just dimensions, right?
If so, just select [Col1], go to the Transform tab > Unpivot Columns > Unpivot Other Columns.
You should then have a single column that contains all of your column headers next to the data that they represent that you can easily filter however you wish.
As a bonus, this will also normalise your data into the optimum structure for use in Power BI.
Pete
Hey, no i just put "DATA" into the first column - what i tried to illustrate is a table with a various amount of columns (since excel files will be weekly added into the folder). But these "weekly" reports will sometimes have 10 sometimes 20 etc. columns. I had to always delete a variable amount of columns from variable positions the only indicator of the Positions and Amonut of columns needed to be deleted will be the Header NAME- this means i need a function which generates a list containing all Header names of columns placed BETWEEN the Columns Named "TOTAL" (there will obviously be more Column Header named TOTAL.
Because of the fact that more than 1 Column is named column - the system automatically renames the 2nd column named Total into Total_"ColNo".
So what i did is creating a function which is then fed into the Table.RemoveColumns(table, fxDynamicColDeletion(table,"Total"))
fxDynamicColDeletion will "clean" all Header names so that Total_"ColID" gets renamed back into "Total"
Than i calculate Pos1 and Pos2 where Pos1 is the first Header named total and pos2 the second.
After having the positions i create a list of HeaderNames by using Table.Range(ColumnNames, Pos1,Pos2) and get the List of Header to be deleted for this particular Table.
I can copy the whole exact code as I return back to the office, but thanks anyway for your help!
Hi padinator ,
Sounds like this issue has been solved by yourself already. If so, glad to hear that and you can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
