Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MagubaneSS
Regular Visitor

Unpivot Multiple Columns

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-RegionCountryProgramme NameSecurity LevelStart DateEnd DateResult LevelResult StatementIndicatorBaseline12 Months Target6 Months Progress12 Months Achieved12 Months DeviationYear 1_Reasons for Over- or Non-AccomplishmentYear 1_Proposed  Action to Adjust Plans24 Months Target18 Months Progress24 Months Achieved24 Months DeviationYear 2_Reasons for Over- or Non-AccomplishmentYear 2_Proposed  Action to Adjust Plans36 Months Target30 Months Progress36 Months Achieved36 Months DeviationYear 3_Reasons for Over- or Non-AccomplishmentYear 3_Proposed  Action to Adjust Plans48 Months Target42 Months Progress48 Months Achieved48 Months DeviationYear 4_Reasons for Over- or Non-AccomplishmentYear 4_Proposed  Action to Adjust PlansOverall TargetOverall AchievedOverall DeviationOverall Reasons for Over- or Non-Accomplishment
AfricaMaliNutrition220212024Objective1.  Eg AAAAAAAAAAAAPercentage of ABABC5%10%0%3%30%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT20%5%10%50%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT30% 20%67%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT25%20%55%220%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT85%88%104%Eg. ASASSEFRGGGT
AfricaNigeriaAgriculture220212024Objective1.  Eg. AAAAAAAAAAAAAAAAAAPercentage of AAAABB15%20%0%5%25%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT30%10%20%67%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT15% 10%67%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT25%10%60%240%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT90%95%106%Eg. ASASSEFRGGGT
AsiaIndiaSMME220212024Outcome1.1. BBBBBBBBBPercentage  DDDAAA20%25%0%10%40%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT10%30%60%600%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT20%047%237%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT30%47%78%260%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT85%195%230%Eg. ASASSEFRGGGT
EuropeEnglandEducation220212024Short Term1.1.1. CCCCCCCCNumber of AAAABBBCC10202420%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT40102050%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT5003162%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT60311118%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT1706639%Eg. ASASSEFRGGGT
North AmericaUSAAgriculture220212024Short Term1.1.2. .Percentage of EFGDSD           20%40%5%00%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT10%20%40%400%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT20%029%145%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT60%29%00%Eg. ASASSEFRGGGTEg. ASASSEFRGGGT130%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

 

3 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

 

 

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"

 

 

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1714455253650.png

 

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.

vxinruzhumsft_1-1714455415890.png

The  second matrix.

vxinruzhumsft_2-1714455439342.png

Then create two slicers.

vxinruzhumsft_3-1714455476131.png

 

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

Output

vxinruzhumsft_5-1714455692289.png

 

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.

 

 

View solution in original post

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

View solution in original post

4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1714455253650.png

 

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.

vxinruzhumsft_1-1714455415890.png

The  second matrix.

vxinruzhumsft_2-1714455439342.png

Then create two slicers.

vxinruzhumsft_3-1714455476131.png

 

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

Output

vxinruzhumsft_5-1714455692289.png

 

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.

 

 

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

lbendlin
Super User
Super User

 

 

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"

 

 

Thank you so much - including identifying my wrong approach.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors