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
Anonymous
Not applicable

Create Heirarchy in Power Query (not bi) Using Columns in Query that pulls from multiple tables

The current query I have is made up of merging multiple tables with some column calculations as well.

Everything works perfectly but their is no structure to the heirarchy.

 

For example, I have [Year] [Quarter] [Month]

When I put these into a pivot table as columns and only show columns with data, it works fine.

ex1.png

 

 

 

 

However, if I want each [Month] to show, even if the data is empty, it shows Jan through Dec four times under [Quater], making 48 columns.

ex2.png

 

 

 

How can I create a heirachy so that only certain month sow under certain quarters, and then weeks under months, etc. ?

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @Anonymous , 

I think you could create data structure like below, then use this in Matrix

let
    Source = List.Dates(#date(2020,1,1),365,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "querter", each Date.QuarterOfYear([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "month", each Date.Month([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "week", each Date.WeekOfMonth([Column1]))
in
    #"Added Custom2"
Column = ROUND( RAND()*10,0)

677.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
dax
Community Support
Community Support

Hi @Anonymous , 

I think you could create data structure like below, then use this in Matrix

let
    Source = List.Dates(#date(2020,1,1),365,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "querter", each Date.QuarterOfYear([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "month", each Date.Month([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "week", each Date.WeekOfMonth([Column1]))
in
    #"Added Custom2"
Column = ROUND( RAND()*10,0)

677.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.