Forum Discussion
M Language - unpivot issue
Hello. I am fairly new to the M language, so any tips are greatly appreciated. I am struggling with the following:
I have a risk table with data in it as follows:
ID Risk CY Yr2 Yr3 Yr4 CYLikelihood Y2Likelihood Y3Likelihood Y4Likelihood
1 Change Management $1 $3 $2 $1 Medium High Medium Medium
2 InformationSecurity $2 $3 $5 $6 Low High High High
I need it to look like this so I can score each risk based on the likelihood and other fields:
ID Risk Year Dollars Likelihood
1 Change Management CY $1 Medium
1 Change Management Y2 $3 High
1 Change Management Y3 $2 Medium
1 Change Management Y4 $1 Medium
2 InformationSecurity CY $2 Low
2 InformationSecurity Y2 $3 High
2 InformationSecurity Y3 $5 High
2 InformationSecurity Y4 $6 High
I've tried two different options to get the solution I need and both times I got it wrong. Thank you in advance.
You can first unpivot the likelihood columns and drill down into the value column so you have a list of unpivoted values.
Next you take the Source again, remove the likelihood columns, unpivot the dollars, remove the "r"'s.
Now you have to glue those parts together with Table.FromColumns.
That requires the second table to be turned into records first.
As finishing touches you need to expand the records and rename some columns.
let Source = RiskTable, // First unpivot the likelihood columns: #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Risk", "CY", "Yr2", "Yr3", "Yr4"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Likelihood"}}), Likelihood1 = #"Renamed Columns"[Likelihood], // Take the source again, remove the likelihood columns, unpivot and rem #"Removed Columns" = Table.RemoveColumns(Source,{"CYLikelihood", "Y2Likelihood", "Y3Likelihood", "Y4Likelihood"}), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"ID", "Risk"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","r","",Replacer.ReplaceText,{"Attribute"}), // Glue the 2 parts together, expand the records and rename some columns: Custom2 = Table.FromColumns({Table.ToRecords(#"Replaced Value"),Likelihood1}), #"Expanded Column1" = Table.ExpandRecordColumn(Custom2, "Column1", {"ID", "Risk", "Attribute", "Value"}, {"ID", "Risk", "Attribute", "Value"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1",{{"Attribute", "Year"}, {"Value", "Dollars"}, {"Column2", "Likelihood"}}) in #"Renamed Columns1"
3 Replies
You can first unpivot the likelihood columns and drill down into the value column so you have a list of unpivoted values.
Next you take the Source again, remove the likelihood columns, unpivot the dollars, remove the "r"'s.
Now you have to glue those parts together with Table.FromColumns.
That requires the second table to be turned into records first.
As finishing touches you need to expand the records and rename some columns.
let Source = RiskTable, // First unpivot the likelihood columns: #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Risk", "CY", "Yr2", "Yr3", "Yr4"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Likelihood"}}), Likelihood1 = #"Renamed Columns"[Likelihood], // Take the source again, remove the likelihood columns, unpivot and rem #"Removed Columns" = Table.RemoveColumns(Source,{"CYLikelihood", "Y2Likelihood", "Y3Likelihood", "Y4Likelihood"}), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"ID", "Risk"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","r","",Replacer.ReplaceText,{"Attribute"}), // Glue the 2 parts together, expand the records and rename some columns: Custom2 = Table.FromColumns({Table.ToRecords(#"Replaced Value"),Likelihood1}), #"Expanded Column1" = Table.ExpandRecordColumn(Custom2, "Column1", {"ID", "Risk", "Attribute", "Value"}, {"ID", "Risk", "Attribute", "Value"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1",{{"Attribute", "Year"}, {"Value", "Dollars"}, {"Column2", "Likelihood"}}) in #"Renamed Columns1"A bit brute force but should do the trick. 2 unpivots actually:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOSMxLT1XwTcxLTE/NTc0rAYqpGCqASGMwaaQAF/FNTckszQUyPDLTM5D5UEasTrSSEZDrmZeWX5SbWJKZnxecmlxalFlSiTAJYqopmDQDkT755QgjkanYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Risk = _t, CY = _t, Yr2 = _t, Yr3 = _t, Yr4 = _t, CYLikelihood = _t, Y2Likelihood = _t, Y3Likelihood = _t, Y4Likelihood = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Risk", type text}, {"CY", Currency.Type}, {"Yr2", Currency.Type}, {"Yr3", Currency.Type}, {"Yr4", Currency.Type}, {"CYLikelihood", type text}, {"Y2Likelihood", type text}, {"Y3Likelihood", type text}, {"Y4Likelihood", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Risk", "CY", "Yr2", "Yr3", "Yr4"}, "Attribute", "Value"), #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Other Columns", {"ID", "Risk", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns1", "Custom", each if Text.StartsWith([Attribute], "CY") and [Attribute.1] = "CY" or Text.StartsWith([Attribute], "Y2") and [Attribute.1] = "Yr2" or Text.StartsWith([Attribute], "Y3") and [Attribute.1] = "Yr3" or Text.StartsWith([Attribute], "Y4") and [Attribute.1] = "Yr4" then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"Hi AKM_Crowley,
Have you tried the solutions provided above? Do the solutions work in your scenario? If the solutions work, could you accept them as solutions to close this thread?
If you still have any question on this issue, feel free to post here. :smileyhappy:
Regards
