Forum Discussion
Helper Ipower 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 H2 | F23 H2 | F23 H2 | F24 H1 | F24 H1 | F24 H1 | F24 Q3 | F24 Q3 | F24 Q3 | Jan 24 | Jan 24 | Jan 24 | Feb 24 | Feb 24 | Feb 24 | |
| A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | |
| Red | 6 | 4 | 2 | 8 | 4 | 2 | 8 | 4 | 2 | 10 | 6 | 2 | 8 | 5 | 2 |
| Green | 4 | 2 | 1 | 2 | 1 | 0 | 2 | 1 | 0 | 2 | 1 | 0 | 3 | 1 | 1 |
| Blue | 5 | 1 | 1 | 2 | 1 | 1 | 2 | 1 | 1 | 2 | 1 | 1 | 2 | 1 | 1 |
| White | 6 | 3 | 1 | 5 | 2 | 1 | 3 | 1 | 1 | 3 | 2 | 1 | 3 | 1 | 1 |
| Total | 21 | 9 | 5 | 18 | 7 | 4 | 15 | 7 | 3 | 17 | 9 | 4 | 16 | 8 | 4 |
Stacked column graph be like -
Thanks in Advance!
Hi Nicks612 ,
You need to select Periods and specify that it be sorted by another column (Index).Sort one column by another column in Power BI - Power BI | Microsoft Learn
Best Regards,
Gao
Community Support TeamIf 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
4 Replies
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"Best Regards,
Gao
Community Support TeamIf 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
Nicks612Hello Anonymous ,
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?
Hi Nicks612 ,
You need to select Periods and specify that it be sorted by another column (Index).Sort one column by another column in Power BI - Power BI | Microsoft Learn
Best Regards,
Gao
Community Support TeamIf 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