M Language - unpivot issue

[AKM_Crowley]

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.

[v-ljerr-msft]

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.

Regards

[Greg_Deckler]

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"

[MarcelBeug · Accepted solution]

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"