Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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? 🙂
Solved! Go to 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"
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"