Forum Discussion
Dynamic columns
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? 🙂
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"
1 Reply
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"