Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a bunch of dates (4/1/2024, 4/2/2024, 4/3/2024, etc) in separate columns. I need all these columns of dates to get into one date hierarchy/one date column in the data tab in powerbi. How can I get it to do so in power query?
Solved! Go to Solution.
Hello @fevgeb,
If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.
I hope this little example helps you:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"4/5/2024" = _t, #"4/6/2024" = _t, #"5/7/2024" = _t]),
Unpivoted = Table.UnpivotOtherColumns(Source, {}, "Dates", "Value"),
Grouped = Table.Group(Unpivoted, {"Dates"}, {{"Value", each List.Sum([Value]), type number}}),
Type = Table.TransformColumnTypes(Grouped,{{"Dates", type date}})
in
Type
Best regards from Germany
Manuel Bolz
🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github
Good day fevgeb,
As well as ManuelBolz's solution you could use Table.ColumnNames to get a list of column names. Then, convert it to a table of column names.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"4/5/2024" = _t, #"4/6/2024" = _t, #"5/7/2024" = _t]),
#"Column Names" = Table.ColumnNames( Source ),
#"Converted to Table" = Table.FromList(#"Column Names", Splitter.SplitByNothing(), type table [Date=datetime], null, ExtraValues.Error)
in
#"Converted to Table"
Hope this helps.
Good day fevgeb,
As well as ManuelBolz's solution you could use Table.ColumnNames to get a list of column names. Then, convert it to a table of column names.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"4/5/2024" = _t, #"4/6/2024" = _t, #"5/7/2024" = _t]),
#"Column Names" = Table.ColumnNames( Source ),
#"Converted to Table" = Table.FromList(#"Column Names", Splitter.SplitByNothing(), type table [Date=datetime], null, ExtraValues.Error)
in
#"Converted to Table"
Hope this helps.
Hello @fevgeb,
If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.
I hope this little example helps you:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"4/5/2024" = _t, #"4/6/2024" = _t, #"5/7/2024" = _t]),
Unpivoted = Table.UnpivotOtherColumns(Source, {}, "Dates", "Value"),
Grouped = Table.Group(Unpivoted, {"Dates"}, {{"Value", each List.Sum([Value]), type number}}),
Type = Table.TransformColumnTypes(Grouped,{{"Dates", type date}})
in
Type
Best regards from Germany
Manuel Bolz
🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github