power query editor - to generate stacked column chart

[Nicks612]

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!

[v-cgao-msft]

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

[Nicks612]

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?

[v-cgao-msft · Accepted solution]

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

[Nicks612]

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!