Forum Discussion

AKM_Crowley's avatar
AKM_Crowley
New Member
9 years ago
Solved

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               ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

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"