How to get multiple columns into one date hierachy/column

[fevgeb]

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? 

[ManuelBolz · Accepted 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

[collinsg · Accepted solution]

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.

[ManuelBolz · Accepted 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