An elegant way to delete all columns beginning with "Budget" (and never load them)

[michellepace]

Hi there,

We don't make use of Budgeting in our accounting software. In the query below I have manually selected all the columns beginning with Budget. 

let
    Source = Odbc.DataSource("dsn=PAS19DEMO", [HierarchicalNavigation=true]),
    PAS19DEMO_Database = Source{[Name="PAS19DEMO",Kind="Database"]}[Data],
    LedgerMaster_Table = PAS19DEMO_Database{[Name="LedgerMaster",Kind="Table"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(LedgerMaster_Table,{"BudgetThis01", "BudgetThis02", "BudgetThis03", "BudgetThis04", "BudgetThis05", "BudgetThis06", "BudgetThis07", "BudgetThis08", "BudgetThis09", "BudgetThis10", "BudgetThis11", "BudgetThis12", "BudgetThis13", "BudgetNext01", "BudgetNext02", "BudgetNext03", "BudgetNext04", "BudgetNext05", "BudgetNext06", "BudgetNext07", "BudgetNext08", "BudgetNext09", "BudgetNext10", "BudgetNext11", "BudgetNext12", "BudgetNext13", "BudgetLast01", "BudgetLast02", "BudgetLast03", "BudgetLast04", "BudgetLast05", "BudgetLast06", "BudgetLast07", "BudgetLast08", "BudgetLast09", "BudgetLast10", "BudgetLast11", "BudgetLast12", "BudgetLast13"})
in
    #"Removed Columns"

Question 1: Is there a short crisp statement I could write to do this?

Question 2: Will PowerBi completely avoid loading any Budget columns? If not, what how do I ensure PowerBi never loads any BudgetColumns?

[v-eachen-msft]

Hi @michellepace ,

For your first question, I think ramsyBI's reply can solve it.
For your second question, Budget columns will be loaded if you remove these columns in the query editor. As long as your source contains Budget column, it will be imported. If you want to avoid loading any Budget columns, you can create a new table which doesn't contain these columns in your datasource(According to your image, it looks like the Oracle). Then import the new table in the Power BI.

[michellepace]

Thank you both for your reply. For Question 1 - - I'd like to learn how to do this selection in code. RamsyBI's is warranted, but I'd really like to know how to do it as a Dax expression. Not really for now, but to learn, for future cases. 

Does anyone know how to do this?

[v-eachen-msft · Accepted solution]

Hi @michellepace ,

You can use SELECTCOLUMNS() function to select the columns you want to keep. However, it can be lengthy if you need to keep many columns. 

There is no way to remove a column based on a column name, because the column name cannot be obtained by a function in DAX.

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtKhiDQEYiNDpVidUcMG1rBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Budget 1" = _t, #"Budget 2" = _t, #"Budget 3" = _t, #"Budget 4" = _t, #"Budget 5" = _t, #"Budget 6" = _t, #"Budget 7" = _t, #"Budget 8" = _t, #"Budget 9" = _t, #"Budget 10" = _t, #"Budget 11" = _t, #"Int 1" = _t, #"Int 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Budget 1", type text}, {"Budget 2", type text}, {"Budget 3", type text}, {"Budget 4", type text}, {"Budget 5", type text}, {"Budget 6", type text}, {"Budget 7", type text}, {"Budget 8", type text}, {"Budget 9", type text}, {"Budget 10", type text}, {"Budget 11", type text}, {"Int 1", Int64.Type}, {"Int 2", Int64.Type}}),
   //Added Steps
    #"Column Names" = Table.ColumnNames(#"Changed Type"),
    #"Columns Containing String" = List.FindText(#"Column Names","Budget"),
    #"List Difference" = List.Difference(#"Column Names", #"Columns Containing String"),
    #"Remove Columns" = Table.SelectColumns(Source, #"List Difference") 
    in
    #"Remove Columns"

You can use above M query to achieve the same.

Tweaked query mention in this link.

[jthomson]

Rather than selecting the columns to remove, why not choose the columns you want to retain, therefore if more budget columns appear they won't appear?

Don't know if they actually get loaded or not