Forum Discussion

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity 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...
Need Help
  • HotChilli's avatar
    HotChilli
    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)

dax's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

Hi rsbin , 

You also could refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY1RCgAhCAWvsvQdlGlSZ4nuf4307eKynyv6GBDHtRIpp5y0ooykUGm1OfoAOEiDZhC1B3c2nRiPToLN18Vxx+/djR3pcWGg6niogwVLyGj+kXnvfQA=", 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 - Percent" = _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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record No.", Int64.Type}, {"Project Amount", Int64.Type}, {"Contract Start Date", type date}, {"Milestone #1 - # of Days", Int64.Type}, {"Milestone 1 - Percent", Int64.Type}, {"Milestone #2 - # of Days", Int64.Type}, {"Milestone #2 - % of $", Int64.Type}, {"Milestone #3 - # of Days", Int64.Type}, {"Milestone #3 - % of $", Int64.Type}, {"Milestone #4 - # of Days", Int64.Type}, {"Milestone #4 - % of $", Int64.Type}, {"Milestone #5 - # of Days", Int64.Type}, {"Milestone #5 - % of $", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record No.", "Project Amount", "Contract Start Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Attribute.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.1.2", "Attribute.1.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1.1", type text}, {"Attribute.1.2", type text}, {"Attribute.1.3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","#","",Replacer.ReplaceText,{"Attribute.1.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","% of $","Percent",Replacer.ReplaceText,{"Attribute.2"}),
    #"Pivoted Column1" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute.2]), "Attribute.2", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Pivoted Column1", List.Distinct(#"Pivoted Column1"[Attribute.1.1]), "Attribute.1.1", "Attribute.1.2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.1.3"})
in
    #"Removed Columns"


Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.