Forum Discussion
Power Query Transform Fields into Records
- 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)
Hello HotChilli
Thanks again for your response. I'm finally at the point where I am trying to integrate your code into my existing Query Editor. However, I am stuck. I create my data table with the following line of code:
= let
Source = Table.SelectRows(#"OpptMaster v1", each [RevenueType]="Incremental" and [ContractStartDate]<>null and [ProjectAmount]<>0 and [Stage_Sort]<>8 and [Stage_Sort]<>7)
in
Source
My next step is to incorporate your code. I do not know where or how to combine your solution with the above. I still have much to learn, but hoping you could provide me with a little guidance. Any help is very much appreciated.
Kind Regards,