Transpose or Unpivot with multiple categories

[SilvijaS]

Hello,

I am a relatively new Power Bi user and I have a dataset  shown in the table below in a simplified way. I have multiple ''descriptive''  categories and multiple Budget and Actual scenarios in columns by month. This table shows only three categories (from which only ITEM has unique values) and the first three months for Budget and Actual 2022. 

The idea is to make a line chart with months on X-axis, and on the Y-axis values for multiple Budget and Actual scenarios, but of course, first I would have to transpose my table which I have done. After transposing I get ''Item'', ''Country'', and ''Area'' in the first three rows which is very inconvenient. I have tried unpivoting multiple ways but it is not working with this data set. 

I must mention that my Excel data has to be organized this way, in other words, I need to keep months in columns because there are multiple categories as shown in the table. Any ideas are very welcome!

Thank you in advance for your responses,

S.

			Budget '22			Actual '22
ITEM	DIVISION	AREA	Jan	Feb	March	Jan	Feb	March
Material	HR	Corporate	50	70	45	20	15	8
Printing	Postal	Corporate	40	40	50	5	7	16
Equipment	Maintenance	Individual	60	30	70	25	7	20
Marketing	Advertising	Corporate	80	30	90	30	9	43
Clients	Advertising	Individual	50	30	10	10	17	5

[rohit_singh]

Hi @SilvijaS ,

Please try this :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/LDoIwFET/hTULeeOSIMaaoASNG8KiQqONWLAUvt/bIg+jm2G4yZycZpmGzlGs6doGXdAJHQ9QgzQK4LPHDHJLrpAx5sX97y3XM2iCcIorOO5SiLDmTc3hCN1ZQXgybAfClM2QzVfThFMmKLvBIalboRjLub0aQ4Hk0JMEV62jV0ebJ2FC6QCJMMwKuUOspD0tOwV05dSaRMyRAjKDPn+Qj0RQ9oQL2g5/SxN/gqznJuUsBQkrCh7tD+JLxJmWxhyeeleevwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #" .1" = _t, #" .2" = _t, #"Budget '22" = _t, #" .3" = _t, #" .4" = _t, #"Actual '22" = _t, #" .5" = _t, #" .6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" ", type text}, {" .1", type text}, {" .2", type text}, {"Budget '22", type text}, {" .3", type text}, {" .4", type text}, {"Actual '22", type text}, {" .5", type text}, {" .6", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Transposed Table", {{"Column1", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter"," ",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEM", type text}, {"DIVISION", type text}, {"AREA", type text}, {"Budget '22Jan", Int64.Type}, {"Budget '22Feb", Int64.Type}, {"Budget '22March", Int64.Type}, {"Actual '22Jan", Int64.Type}, {"Actual '22Feb", Int64.Type}, {"Actual '22March", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ITEM", "DIVISION", "AREA"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Character Transition",{{"Attribute.1", "Attribute"}, {"Attribute.2", "Month"}})
in
    #"Renamed Columns"

This is the final result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[SilvijaS]

Hi,

thank you for your answer but idea was to get 'Budget 22' and 'Actual 22' into separate columns if possible, that way I can have two lines on my line chart.

Kind regards,

S.

[rohit_singh · Accepted solution]

Hello @SilvijaS ,

No worries that's pretty straightforward. You just need to add an additional line of code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/LDoIwFET/hTULeeOSIMaaoASNG8KiQqONWLAUvt/bIg+jm2G4yZycZpmGzlGs6doGXdAJHQ9QgzQK4LPHDHJLrpAx5sX97y3XM2iCcIorOO5SiLDmTc3hCN1ZQXgybAfClM2QzVfThFMmKLvBIalboRjLub0aQ4Hk0JMEV62jV0ebJ2FC6QCJMMwKuUOspD0tOwV05dSaRMyRAjKDPn+Qj0RQ9oQL2g5/SxN/gqznJuUsBQkrCh7tD+JLxJmWxhyeeleevwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #" .1" = _t, #" .2" = _t, #"Budget '22" = _t, #" .3" = _t, #" .4" = _t, #"Actual '22" = _t, #" .5" = _t, #" .6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" ", type text}, {" .1", type text}, {" .2", type text}, {"Budget '22", type text}, {" .3", type text}, {" .4", type text}, {"Actual '22", type text}, {" .5", type text}, {" .6", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Transposed Table", {{"Column1", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter"," ",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEM", type text}, {"DIVISION", type text}, {"AREA", type text}, {"Budget '22Jan", Int64.Type}, {"Budget '22Feb", Int64.Type}, {"Budget '22March", Int64.Type}, {"Actual '22Jan", Int64.Type}, {"Actual '22Feb", Int64.Type}, {"Actual '22March", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ITEM", "DIVISION", "AREA"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Character Transition",{{"Attribute.1", "Attribute"}, {"Attribute.2", "Month"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Value", List.Sum)

This is the result

 

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂