How to unpivot this type of table

[ac__sgccck]

Hi there,

Above is the table I have and I want to unpivot it such that I can get my data organized like below. And, when there is any update (e.g. more columns added for CC4, CC5…), I can refresh data. I think of using Power Query , but not able to get it done.  Thanks!

CC1  - Unit ID 1 - %

CC1 – Unit ID2 - %

CC2 – Unit ID1 - %

CC2 – Unit ID2 - %

CC3 - Unit ID 1 - %

...

[v-sgandrathi · Accepted solution]

Hi @ac__sgccck,
Thank you for reaching out to the Microsoft Fabric Community forum.

Based on the information provided, we loaded the data into Power BI, accessed the "Transform Data" option, unpivoted the necessary columns, and carried out the following steps:

Close and apply the changes to load the data back into Power BI. Next, go to the Report view, select a visual like a matrix or table, and use it to display the data. If you add new data to the existing table in the source file and refresh the report, the updated data will automatically appear in the visual.

For your Idea, I’ve attached the PBIX file with the implemented solution. Please review it and let us know is there any further questions.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more easily.

Best Regards,

Sahasra.

[ronrsnfld · Accepted solution]

Given your original data:

You can use this code which should automatically adjust to addition/removal of Cost Centers provided that your data layout remains the same as you show. Read the code comments for more information.

let

//Replace Next Line with actual data source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBNDsIgEIWvYkjcdcEMUmDZUk0Xahqakpim979GB0hEkbpgfsI3j8esK7PDwhrWTTMiZUQASpqfKZ6+ztaUMCQYeKS1NiZ35QxFEW4V5yVUhTHBH/rUyZq+7yzVd0+moqcW/z2QaD8sJkkqSkIcsL2j+jn7Xma4VdEMXkTNzPgK8o9ptDJvEw+8uOstfNa6qA74Xjw1v+rbDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Area Code" = _t, #"Unit ID" = _t, #"Cost Center 1" = _t, #"Cost Center 1 -%" = _t, #" Cost Center 2" = _t, #" Cost Center 2-%" = _t, #" Cost Center 3" = _t, #"Cost Center 3-%" = _t]),

//Set data types assuming first two columns are text, and remainder alternate whole number / Percent
//  This will adapt as Cost Centers are added
    #"Changed Type" = Table.TransformColumnTypes(Source,
    let 
                cols = List.Skip(Table.ColumnNames(Source),2),
                percents = List.Alternate(cols,1,1),
                numbers = List.Alternate(cols,1,1,1),
                textTypes = List.Transform(List.FirstN(Table.ColumnNames(Source),2), each {_, type text}),
                percentTypes = List.Transform(percents, each {_, Percentage.Type}),
                numberTypes = List.Transform(numbers,each {_, Int64.Type})
            in List.Combine({textTypes,numberTypes,percentTypes})),

//Remove Area Code since we don't need it in final report
    #"Removed Area Code" = Table.RemoveColumns(#"Changed Type",{"Area Code"}),
    
//Unpivot columns other than Unit ID
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Area Code", {"Unit ID"}, "Attribute", "Value"),

//Only those rows that represent Percents
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.EndsWith([Attribute], "%")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Attribute", "Unit ID", "Value"}),

//Extract the Cost Center number for sorting purposes
//  Then sort and remove the sorting column
//  (Assumes the sorting value will always be the first substring of digits in the Cost Center name).
//  (Might need a different algorithm depending on your actual data)
    #"Add Cost Center Sort" = Table.AddColumn(#"Reordered Columns","Sort", each Number.From(
        Splitter.SplitTextByCharacterTransition({"0".."9"}, (c)=>not List.Contains({"0".."9"},c)) 
        (Splitter.SplitTextByCharacterTransition((c)=>not List.Contains({"0".."9"},c), {"0".."9"})([Attribute]){1}){0}), Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Add Cost Center Sort",{{"Sort", Order.Ascending}, {"Unit ID", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Sort"}),
    
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Value", Percentage.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Cost Center"}})
in
    #"Renamed Columns"

From your data we obtain this result, which I believe is what you want:

Note that the normal unpivot removes columns that are null (such as Cost Center 3). If you want to retain this, insert a step where you replace the nulls in those columns with "" or 0 before doing the unpivot.

[Omid_Motamedise]

Select columns Area code and Code ID, right click on one of them and pick unpivot other columns

[ac__sgccck]

pasted data as table here. thanks!

Area Code	Unit ID	Cost Center 1	Cost Center 1 -%	Cost Center 2	Cost Center 2-%	Cost Center 3	Cost Center 3-%
CDU	APS22	2211	80%
CDU	APS22	1211	100%	8899	100%
CDU	CDU3	1700	100%
CDU	CDU2	1701	100%	1705	100%
VAC	LVPS2	1262	100%
VAC	VDU9	1707	33%
VBR	NSVB5	1707	67%	1243	100%
HYC	MPHC5	2211	20%
REF	CCR	1712	80%	1713	100%

[v-sgandrathi · Accepted solution]

Hi @ac__sgccck,
Thank you for reaching out to the Microsoft Fabric Community forum.

Based on the information provided, we loaded the data into Power BI, accessed the "Transform Data" option, unpivoted the necessary columns, and carried out the following steps:

Close and apply the changes to load the data back into Power BI. Next, go to the Report view, select a visual like a matrix or table, and use it to display the data. If you add new data to the existing table in the source file and refresh the report, the updated data will automatically appear in the visual.

For your Idea, I’ve attached the PBIX file with the implemented solution. Please review it and let us know is there any further questions.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more easily.

Best Regards,

Sahasra.

[v-sgandrathi]

Hi @ac__sgccck,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answerand click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @ac__sgccck ,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Regards,
Sahasra.

[v-sgandrathi]

Hi @ac__sgccck,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Sahasra.