Dynamically delete column names

[Sofimerce1]

Hello everyone, I am trying to dynamically delete column names. 

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. 

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. 

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. 

Any way of helping? @parry2k @amitchandak @Greg_Deckler or anyone else! 🙂

[christinepayton]

If you were to "unpivot other columns" with the first column selected, you'd be able to filter out "Budget" pretty easily in the UI just as a column-level filter. 

[parry2k]

@Sofimerce1 you can paste the following code and there is one step I added which will remove the budget columns dynamically.

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"

[parry2k]

@Sofimerce1 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.

[Sofimerce1]

Thank you!!

Model 1: I want to remove those column that have Budget - starts in DX

Model 2: I want to remove those column that have Budget - starts in DW

Let me knw if this gives you clarity. Thank you!!!

[foodd]

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

[Sofimerce1]

Gotcha, thanks.

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. 

Let me know if this is more helpful. Thanks.