Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
fevgeb
Frequent Visitor

How to get multiple columns into one date hierachy/column

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? 

 

Screenshot 2024-06-10 110322.png

2 ACCEPTED SOLUTIONS
ManuelBolz
Responsive Resident
Responsive Resident

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

View solution in original post

collinsg
Super User
Super User

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.

View solution in original post

2 REPLIES 2
collinsg
Super User
Super User

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
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.