Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
padinator
Helper I
Helper I

Dynamically remov columns in power query

Hello, i have the following issue in power query. Since i will dynamically load new excel files into my Folder which is going to be modelled by my power query logic AND these files will have different amounts of Columns (based on the Number of Months i am going to export each time) i am looking for a dynamic way to delete certain columns .

 

My idea was to identify the columns which need to be deleted by an unique identified which is the column "TOTAL".

This means - all Columns which are placed between "TOTAL" columns need to be deleted - including the Total columns ! This means - the TOTAL Columns appear more than once within my header which brought me to my next problem, since power query automatically renames the second columns to TOTAL_"ColID"

 

To illustrate the problem visually

 

Col1Col2TOTALCol4Col5TOTAL_6Col7Col8Col9
DATA        

 

Now i would need to have a function which returns a list of {TOTAL,Col4,Col5,TOTAL_6}

I would have managed to do this is the second total wouldn't have been automatically renamed so that List.PositionOf(ColNames,TOTAL) isn't working generically anymore.

 

Any ideas or hints would be highly appreciated!!

 

1 ACCEPTED SOLUTION
padinator
Helper I
Helper I

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
ColsToRemove

 

I 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.

View solution in original post

4 REPLIES 4
padinator
Helper I
Helper I

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
ColsToRemove

 

I 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.

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors