Dynamically delete column names

Question

Hello everyone,&nbsp;I am trying to dynamically delete column names.&nbsp;&nbsp;Background on my query: my source is linked to a sharepoint folder that contains multiple Excel sheets and I upload a new one everyday. The data I use for the Power BI is the most recent upload. So the source is the folder then I filter based on Upload date for the latest file, then expand the most recent upload to get the content.&nbsp;&nbsp;I tried creating a list of all the column names from the source and find text (as explained in this video) because my source is the sharepoint folder that includes all of the Excel files. So when I create the Column name list it shows me the attributes of the folder "File Name, Date Modified, Date Uploaded, etc)- not the column names within those files.&nbsp;&nbsp;I am trying to delete headers that contain the word "Budget". There are hundreds of columns and every file has a different location of Budget columns.&nbsp;&nbsp;Any way of helping?&nbsp;parry2k&nbsp;amitchandak&nbsp;Greg_Deckler&nbsp;or anyone else! 🙂

anonymous · Answer

Gotcha, thanks.&nbsp;Here you will find the initial data set. And now here you will find an updated version that will replace the previous data set. As you can see two columns were added: One in the middle of the data set, and one at the end. I want to dynamically delete those columns that have "Budget" but I cannot delete the last x amount of columns because the Budget Columns shift continously. Also, I cannot make a List the Source Columns because the source is the folder.&nbsp;&nbsp;Let me know if this is more helpful. Thanks.&nbsp;

parry2k · Answer

Anonymous&nbsp;you can paste the following code and there is one step I added which will remove the budget columns dynamically.
&nbsp;
&nbsp;
let
    Source = Excel.Workbook(File.Contents("C:\Users\parvi\Downloads\Week 4 - sample file.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Table Remove Budget Columns" = Table.SelectColumns(#"Promoted Headers", List.RemoveMatchingItems(Table.ColumnNames(#"Promoted Headers"), List.Select(Table.ColumnNames(#"Promoted Headers"), each Text.Contains(_, "Budget"))))
in 
#"Table Remove Budget Columns"

&nbsp;
&nbsp;

parry2k · Answer

Anonymous&nbsp;if you share a sample excel file (remove sensitive information) and what columns you want to remove or keep, provide the rules, will get you the solution.

anonymous · Answer

Thank you!!&nbsp;Model 1: I want to remove those column that have Budget - starts in DX&nbsp;Model 2:&nbsp;I want to remove those column that have Budget - starts in DW&nbsp;Let me knw if this gives you clarity. Thank you!!!

foodd · Answer

Not an image of parts of a worksheet, the actual workbook, that can be uploaded to OneDrive, BOX or the like, and shared anonymously. Please then reply with the sharing link.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Forum Discussion

Dynamically delete column names

6 Replies

Related content

Dynamic Column headers

Dynamic Column Name

column deleted but column exists

Dynamic Variable in Calculated Columns

Dynamic column comparison of last column