Unpivot Multiple Columns

[MagubaneSS]

Hi Experts,

Im new to power query and am struggling to transform my data so it is easy to analyze in power-bi.

Below is my sample data:

Sub-Region	Country	Programme Name	Security Level	Start Date	End Date	Result Level	Result Statement	Indicator	Baseline	12 Months Target	6 Months Progress	12 Months Achieved	12 Months Deviation	Year 1_Reasons for Over- or Non-Accomplishment	Year 1_Proposed  Action to Adjust Plans	24 Months Target	18 Months Progress	24 Months Achieved	24 Months Deviation	Year 2_Reasons for Over- or Non-Accomplishment	Year 2_Proposed  Action to Adjust Plans	36 Months Target	30 Months Progress	36 Months Achieved	36 Months Deviation	Year 3_Reasons for Over- or Non-Accomplishment	Year 3_Proposed  Action to Adjust Plans	48 Months Target	42 Months Progress	48 Months Achieved	48 Months Deviation	Year 4_Reasons for Over- or Non-Accomplishment	Year 4_Proposed  Action to Adjust Plans	Overall Target	Overall Achieved	Overall Deviation	Overall Reasons for Over- or Non-Accomplishment
Africa	Mali	Nutrition	2	2021	2024	Objective	1.  Eg AAAAAAAAAAAA	Percentage of ABABC	5%	10%	0%	3%	30%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	20%	5%	10%	50%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	30%		20%	67%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	25%	20%	55%	220%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	85%	88%	104%	Eg. ASASSEFRGGGT
Africa	Nigeria	Agriculture	2	2021	2024	Objective	1.  Eg. AAAAAAAAAAAAAAAAAA	Percentage of AAAABB	15%	20%	0%	5%	25%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	30%	10%	20%	67%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	15%		10%	67%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	25%	10%	60%	240%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	90%	95%	106%	Eg. ASASSEFRGGGT
Asia	India	SMME	2	2021	2024	Outcome	1.1. BBBBBBBBB	Percentage  DDDAAA	20%	25%	0%	10%	40%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	10%	30%	60%	600%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	20%	0	47%	237%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	30%	47%	78%	260%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	85%	195%	230%	Eg. ASASSEFRGGGT
Europe	England	Education	2	2021	2024	Short Term	1.1.1. CCCCCCCC	Number of AAAABBBCC	10	20	2	4	20%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	40	10	20	50%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	50	0	31	62%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	60	31	11	18%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	170	66	39%	Eg. ASASSEFRGGGT
North America	USA	Agriculture	2	2021	2024	Short Term	1.1.2. .	Percentage of EFGDSD	20%	40%	5%	0	0%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	10%	20%	40%	400%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	20%	0	29%	145%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	60%	29%	0	0%	Eg. ASASSEFRGGGT	Eg. ASASSEFRGGGT	130%	69%	53%	Eg. ASASSEFRGGGT

In my output, I would like:

• All the target columns (12 months, 24 months, 36 months, etc.) to be represented in 1 column
• All the actual (mid-year progress and annual achievement) to be represented in 1 column
• All the deviation columns to be represented in 1 column
• All the reason for over or non-accomplishment columns to be represented in 1 column, and
• All the proposed action to adjust plans columns to be under 1 column

After unpivoting (pivoting) to the above output, please also advise on number formatting for the target, actual, and deviation columns - seeing that some numbers are 'whole numbers' and some are 'percentages'.

Thanking you in advance for your support

[lbendlin · Accepted solution]

All the target columns (12 months, 24 months, 36 months, etc.) to be represented in 1 column

How?  You have three dimensions (Interval, type (target), and Value).  You cannot cram that into a two dimensional storage.

let
    Source = <your table>,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Indicator", "Result Statement", "Result Level", "End Date", "Start Date", "Security Level", "Programme Name", "Country", "Sub-Region", "Baseline"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Months ","Months_",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Interval", "Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Interval] <> "Overall Achieved" and [Interval] <> "Overall Deviation" and [Interval] <> "Overall Reasons for Over- or Non-Accomplishment" and [Interval] <> "Overall Target"))
in
    #"Filtered Rows"

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @lbendlin provided, and  i want to offer some more information for user to refer to.

hello @MagubaneSS , because you want to change the data format, it cannot define it in power query, so you can consider to unpivot all the columns first, then use dynamic format of measure to implement it.

1.Put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVRRb4IwEP4rjcneDIECVR+LoNmDbhnuyfiAWJFFYOlgv3/ttSJmkFAvob1c+O647+Nuv5/QM8/TZDKdbJJrLq5tU/O8zqtS+Fg+NnbU5Ynr7fjF0jr/ZcJ3LISiDNGOieg74ykr6yRjqDojGtBgKaL+iwTY8oTDhQPcKLMQjWkcR6uP9Xq96w9heLeTxjcAq0KoTUNmBoWhpi6vfGxQeg6Q+Vx9uNcLPEw7MmzzjPFcejQToeZaN5yN1MJ6EGNIEmFBIDGdzu7sqn5NeFV6mDOr6qM2g7kqGqfqewaqLODdhU5ChlX5ASVeyxPc8WYT9UvR1GlVKCGEFMHNHslHYRgqRRRZqokOhyYtKITbYYDYxvNky6JAO3YN2FdVFXAGvzYmxjPhKPrxwBaQ9EcNr74lq1GZXZPyJL1TkyaD6ym+VLxGO8YLLYUQY6kNdltxZPw+BAFEHRsS6IReS864Zjz7IYXJWvJtLYErWyB4PJK0KAeOucF/M5NYQmSCxSDzW8HjBdGC6a30GdMRG+kf/dhC1r8FFK3WYRyiu7WMe/c1ZN8mw2Qaulm856YBAyeOZ7AF9f5ZPPPZen4B67v9chz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sub-Region" = _t, Country = _t, #"Programme Name" = _t, #"Security Level" = _t, #"Start Date" = _t, #"End Date" = _t, #"Result Level" = _t, #"Result Statement" = _t, Indicator = _t, Baseline = _t, #"12 Months Target" = _t, #"6 Months Progress" = _t, #"12 Months Achieved" = _t, #"12 Months Deviation" = _t, #"Year 1_Reasons for Over- or Non-Accomplishment" = _t, #"Year 1_Proposed  Action to Adjust Plans" = _t, #"24 Months Target" = _t, #"18 Months Progress" = _t, #"24 Months Achieved" = _t, #"24 Months Deviation" = _t, #"Year 2_Reasons for Over- or Non-Accomplishment" = _t, #"Year 2_Proposed  Action to Adjust Plans" = _t, #"36 Months Target" = _t, #"30 Months Progress" = _t, #"36 Months Achieved" = _t, #"36 Months Deviation" = _t, #"Year 3_Reasons for Over- or Non-Accomplishment" = _t, #"Year 3_Proposed  Action to Adjust Plans" = _t, #"48 Months Target" = _t, #"42 Months Progress" = _t, #"48 Months Achieved" = _t, #"48 Months Deviation" = _t, #"Year 4_Reasons for Over- or Non-Accomplishment" = _t, #"Year 4_Proposed  Action to Adjust Plans" = _t, #"Overall Target" = _t, #"Overall Achieved" = _t, #"Overall Deviation" = _t, #"Overall Reasons for Over- or Non-Accomplishment" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sub-Region", type text}, {"Country", type text}, {"Programme Name", type text}, {"Security Level", Int64.Type}, {"Start Date", Int64.Type}, {"End Date", Int64.Type}, {"Result Level", type text}, {"Result Statement", type text}, {"Indicator", type text}, {"Baseline", type number}, {"12 Months Target", type number}, {"6 Months Progress", type number}, {"12 Months Achieved", type number}, {"12 Months Deviation", Percentage.Type}, {"Year 1_Reasons for Over- or Non-Accomplishment", type text}, {"Year 1_Proposed  Action to Adjust Plans", type text}, {"24 Months Target", type number}, {"18 Months Progress", type number}, {"24 Months Achieved", type number}, {"24 Months Deviation", Percentage.Type}, {"Year 2_Reasons for Over- or Non-Accomplishment", type text}, {"Year 2_Proposed  Action to Adjust Plans", type text}, {"36 Months Target", type number}, {"30 Months Progress", Int64.Type}, {"36 Months Achieved", type number}, {"36 Months Deviation", Percentage.Type}, {"Year 3_Reasons for Over- or Non-Accomplishment", type text}, {"Year 3_Proposed  Action to Adjust Plans", type text}, {"48 Months Target", type number}, {"42 Months Progress", type number}, {"48 Months Achieved", type number}, {"48 Months Deviation", Percentage.Type}, {"Year 4_Reasons for Over- or Non-Accomplishment", type text}, {"Year 4_Proposed  Action to Adjust Plans", type text}, {"Overall Target", type number}, {"Overall Achieved", type number}, {"Overall Deviation", Percentage.Type}, {"Overall Reasons for Over- or Non-Accomplishment", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sub-Region", "Country", "Programme Name", "Security Level", "Start Date", "End Date", "Result Level", "Result Statement", "Indicator", "Baseline"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Attribute_type", each if Text.Contains([Attribute],"Target") then "Target" else if Text.Contains([Attribute],"Progress") or Text.Contains([Attribute],"Achieved") then "Actual" else if Text.Contains([Attribute],"Deviation") then "Deviation" else if Text.Contains([Attribute],"Reasons") then "Reason for over or non-accomplishment" else "Proposed action to adjust plans"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if not Text.Contains([Value], "Eg") then [Value] else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}})
in
    #"Changed Type2"

2.You can create a matrix and create a measure.

Measure = SUM(Query1[Custom])

select the dynamic format of the measure.

Then input the following code to the format.

SWITCH(TRUE(),SELECTEDVALUE(Query1[Sub-Region])="Europe"&&SELECTEDVALUE(Query1[Attribute_type])="Deviation","###","0.00%")

Then you can create two matrix and input the following field.

The first matrix.

The  second matrix.

Then create two slicers.

Then set the interaction of the second matrix will not affected by other visuals.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @lbendlin provided, and  i want to offer some more information for user to refer to.

hello @MagubaneSS , because you want to change the data format, it cannot define it in power query, so you can consider to unpivot all the columns first, then use dynamic format of measure to implement it.

1.Put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVRRb4IwEP4rjcneDIECVR+LoNmDbhnuyfiAWJFFYOlgv3/ttSJmkFAvob1c+O647+Nuv5/QM8/TZDKdbJJrLq5tU/O8zqtS+Fg+NnbU5Ynr7fjF0jr/ZcJ3LISiDNGOieg74ykr6yRjqDojGtBgKaL+iwTY8oTDhQPcKLMQjWkcR6uP9Xq96w9heLeTxjcAq0KoTUNmBoWhpi6vfGxQeg6Q+Vx9uNcLPEw7MmzzjPFcejQToeZaN5yN1MJ6EGNIEmFBIDGdzu7sqn5NeFV6mDOr6qM2g7kqGqfqewaqLODdhU5ChlX5ASVeyxPc8WYT9UvR1GlVKCGEFMHNHslHYRgqRRRZqokOhyYtKITbYYDYxvNky6JAO3YN2FdVFXAGvzYmxjPhKPrxwBaQ9EcNr74lq1GZXZPyJL1TkyaD6ym+VLxGO8YLLYUQY6kNdltxZPw+BAFEHRsS6IReS864Zjz7IYXJWvJtLYErWyB4PJK0KAeOucF/M5NYQmSCxSDzW8HjBdGC6a30GdMRG+kf/dhC1r8FFK3WYRyiu7WMe/c1ZN8mw2Qaulm856YBAyeOZ7AF9f5ZPPPZen4B67v9chz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sub-Region" = _t, Country = _t, #"Programme Name" = _t, #"Security Level" = _t, #"Start Date" = _t, #"End Date" = _t, #"Result Level" = _t, #"Result Statement" = _t, Indicator = _t, Baseline = _t, #"12 Months Target" = _t, #"6 Months Progress" = _t, #"12 Months Achieved" = _t, #"12 Months Deviation" = _t, #"Year 1_Reasons for Over- or Non-Accomplishment" = _t, #"Year 1_Proposed  Action to Adjust Plans" = _t, #"24 Months Target" = _t, #"18 Months Progress" = _t, #"24 Months Achieved" = _t, #"24 Months Deviation" = _t, #"Year 2_Reasons for Over- or Non-Accomplishment" = _t, #"Year 2_Proposed  Action to Adjust Plans" = _t, #"36 Months Target" = _t, #"30 Months Progress" = _t, #"36 Months Achieved" = _t, #"36 Months Deviation" = _t, #"Year 3_Reasons for Over- or Non-Accomplishment" = _t, #"Year 3_Proposed  Action to Adjust Plans" = _t, #"48 Months Target" = _t, #"42 Months Progress" = _t, #"48 Months Achieved" = _t, #"48 Months Deviation" = _t, #"Year 4_Reasons for Over- or Non-Accomplishment" = _t, #"Year 4_Proposed  Action to Adjust Plans" = _t, #"Overall Target" = _t, #"Overall Achieved" = _t, #"Overall Deviation" = _t, #"Overall Reasons for Over- or Non-Accomplishment" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sub-Region", type text}, {"Country", type text}, {"Programme Name", type text}, {"Security Level", Int64.Type}, {"Start Date", Int64.Type}, {"End Date", Int64.Type}, {"Result Level", type text}, {"Result Statement", type text}, {"Indicator", type text}, {"Baseline", type number}, {"12 Months Target", type number}, {"6 Months Progress", type number}, {"12 Months Achieved", type number}, {"12 Months Deviation", Percentage.Type}, {"Year 1_Reasons for Over- or Non-Accomplishment", type text}, {"Year 1_Proposed  Action to Adjust Plans", type text}, {"24 Months Target", type number}, {"18 Months Progress", type number}, {"24 Months Achieved", type number}, {"24 Months Deviation", Percentage.Type}, {"Year 2_Reasons for Over- or Non-Accomplishment", type text}, {"Year 2_Proposed  Action to Adjust Plans", type text}, {"36 Months Target", type number}, {"30 Months Progress", Int64.Type}, {"36 Months Achieved", type number}, {"36 Months Deviation", Percentage.Type}, {"Year 3_Reasons for Over- or Non-Accomplishment", type text}, {"Year 3_Proposed  Action to Adjust Plans", type text}, {"48 Months Target", type number}, {"42 Months Progress", type number}, {"48 Months Achieved", type number}, {"48 Months Deviation", Percentage.Type}, {"Year 4_Reasons for Over- or Non-Accomplishment", type text}, {"Year 4_Proposed  Action to Adjust Plans", type text}, {"Overall Target", type number}, {"Overall Achieved", type number}, {"Overall Deviation", Percentage.Type}, {"Overall Reasons for Over- or Non-Accomplishment", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sub-Region", "Country", "Programme Name", "Security Level", "Start Date", "End Date", "Result Level", "Result Statement", "Indicator", "Baseline"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Attribute_type", each if Text.Contains([Attribute],"Target") then "Target" else if Text.Contains([Attribute],"Progress") or Text.Contains([Attribute],"Achieved") then "Actual" else if Text.Contains([Attribute],"Deviation") then "Deviation" else if Text.Contains([Attribute],"Reasons") then "Reason for over or non-accomplishment" else "Proposed action to adjust plans"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if not Text.Contains([Value], "Eg") then [Value] else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}})
in
    #"Changed Type2"

2.You can create a matrix and create a measure.

Measure = SUM(Query1[Custom])

select the dynamic format of the measure.

Then input the following code to the format.

SWITCH(TRUE(),SELECTEDVALUE(Query1[Sub-Region])="Europe"&&SELECTEDVALUE(Query1[Attribute_type])="Deviation","###","0.00%")

Then you can create two matrix and input the following field.

The first matrix.

The  second matrix.

Then create two slicers.

Then set the interaction of the second matrix will not affected by other visuals.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MagubaneSS · Accepted solution]

Thank you so much for taking the time to work on this - much appreciated!

[lbendlin · Accepted solution]

All the target columns (12 months, 24 months, 36 months, etc.) to be represented in 1 column

How?  You have three dimensions (Interval, type (target), and Value).  You cannot cram that into a two dimensional storage.

let
    Source = <your table>,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Indicator", "Result Statement", "Result Level", "End Date", "Start Date", "Security Level", "Programme Name", "Country", "Sub-Region", "Baseline"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Months ","Months_",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Interval", "Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Interval] <> "Overall Achieved" and [Interval] <> "Overall Deviation" and [Interval] <> "Overall Reasons for Over- or Non-Accomplishment" and [Interval] <> "Overall Target"))
in
    #"Filtered Rows"

[MagubaneSS]

Thank you so much - including identifying my wrong approach.