Forum Discussion

rsbin's avatar
rsbin
Community Champion
6 years ago
Solved

Power Query Transform Fields into Records

I have imported a table into Power BI like shown below: The data comes from a CRM application. Record No. Project Amount Contract Start Date Milestone #1 - # of Days Milestone 1 - Percent Mi...
  • HotChilli's avatar
    6 years ago

    If 'Milestone 1 - Percent' column heading is changed to follow the pattern of the other % columns then this will do it 

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY1LCsAgDESvUlwLmi96FvH+11CnJaXbBjI8CPMyRiKXlJNXzCYtVLjywbMACfKgHkT84Mxbp5ubkeLydUn05O3daMgTFxYqw0NvojhCRv2HbC4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Record No." = _t, #"Project Amount" = _t, #"Contract Start Date" = _t, #"Milestone #1 - # of Days" = _t, #"Milestone #1 - % of $" = _t, #"Milestone #2 - # of Days" = _t, #"Milestone #2 - % of $" = _t, #"Milestone #3 - # of Days" = _t, #"Milestone #3 - % of $" = _t, #"Milestone #4 - # of Days" = _t, #"Milestone #4 - % of $" = _t, #"Milestone #5 - # of Days" = _t, #"Milestone #5 - % of $" = _t]),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Record No.", "Project Amount", "Contract Start Date"}, "Attribute", "Value"),
        #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}, {"Contract Start Date", type date}}),
        #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByPositions({0, 13}, false), {"Attribute.1", "Attribute.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Record No.", Int64.Type}, {"Project Amount", Int64.Type}, {"Attribute.1", type text}, {"Attribute.2", type text}}),
        #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value", List.Sum),
        #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Record No.", Order.Descending}, {"Attribute.1", Order.Ascending}})
    in
        #"Sorted Rows"

     A little bit of tidying still to do (filter out null values and rename columns as required)