Reply
Nicks612
Helper I
Helper I
Partially syndicated - Outbound

power query editor - to generate stacked column chart

Hello everyone,

I need help to generate a graph from the below table - but facing issues in Power BI Query Editor after loading this table to PBI. I have attached resulted graph below.

 

 F23 H2F23 H2F23 H2F24 H1F24 H1F24 H1F24 Q3F24 Q3F24 Q3Jan 24Jan 24Jan 24Feb 24Feb 24Feb 24
 ABCABCABCABCABC
Red6428428421062852
Green421210210210311
Blue511211211211211
White631521311321311
Total21951874157317941684

 

Stacked column graph be like - 

 

Nicks612_0-1721669287588.png

 

Thanks in Advance!

1 ACCEPTED SOLUTION

Syndicated - Outbound

Hi @Nicks612 ,

You need to select Periods and specify that it be sorted by another column (Index).

vcgaomsft_0-1721807011702.png

Sort one column by another column in Power BI - Power BI | Microsoft Learn

vcgaomsft_1-1721807247816.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Nicks612 ,

 

Please follow these steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcjMyVvAwwsEwUfAwxMkINMbKMDLR9UrMw8VwS03CwojVAbvFEYidgNiZZDbIgKDUFCDbDIhNQCYDsQUOtqEBVCFMwhTMBhniXpSamoesFIk2wMM2hrINwaY45ZSmQo01RDOEODbIkPCMzJJUqENhxpsiqTJG0mmMVRxkSkh+SWIOSBYkZAlzFMjT5lBvGppCOWCN5lBlYBkzeMDFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Total"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"", "_1"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Colors"}, {"_1", "Category"}, {"", "Periods"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

vcgaomsft_0-1721707991784.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Syndicated - Outbound

Hello @v-cgao-msft ,

 

Thanks for the prompt response! The solution you have given is valuable. I have one query related to this - The X-axis should be in ascending order as shown in my attached graph (main content). How can we do that?

Syndicated - Outbound

Hi @Nicks612 ,

You need to select Periods and specify that it be sorted by another column (Index).

vcgaomsft_0-1721807011702.png

Sort one column by another column in Power BI - Power BI | Microsoft Learn

vcgaomsft_1-1721807247816.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Syndicated - Outbound

hello @v-cgao-msft ,

 

Thank you so much for the support! I have tried column indexing, but it took different numbers for the same values. I learn new tricks to solve these kind of problems. 

 

thanks a lot!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)