Dynamic columns

[hedin123]

Hello all!

I am currently trying to extract data from a webpage for my client. One of the columns I am trying to extract from changes month every time as it is "used money". The format of the column is right now for instance  mm/YY 02-21. So when We move to february it will be 03-21. and then PowerBi each month says: "The column mm/yy-21 was not found". Anyone who knows how I can make my column headers dynamic? 🙂 

[d_gosbell · Accepted solution]

So there are 2 potential solutions to grabbing a column whose name changes from month to month. But you basically need to rename it to a constant value so that your data model does not have to change.

You could either calculate the name of the column using something like DateTime.ToText(DateTime.LocalNow(), "MM/yy") as per the 3rd step in the query below

let
    Source = Csv.Document(File.Contents("C:\Users\darren.gosbell\Documents\dynamic-col.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{DateTime.ToText(DateTime.LocalNow(), "MM/yy"), "UsedMoney"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column1", type text}, {"UsedMoney", Int64.Type}})
in
    #"Changed Type"

Or you could potentially just rename the column according to it's position in the dataset (note that the indexing starts from 0 so if this is the 10th column it would be in position {9} ). In my example I was using a simple 2 column file and the 2nd column was dynamic so I used Table.ColumnNames(#"Promoted Headers"){1} to get the current name, then renamed that to "UsedMoney"

let
    Source = Csv.Document(File.Contents("C:\Users\darren.gosbell\Documents\dynamic-col.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){1}, "UsedMoney"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column1", type text}, {"UsedMoney", Int64.Type}})
in
    #"Changed Type"