Forum Discussion
Community ChampionPower Query Transform Fields into Records
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)
Community ChampionIf '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)
Community ChampionHello 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,
- HotChilli
You have two choices:
1) Learn the general idea of the algorithm. That is, use the sample you have shown. Change the column heading of "Milestone 1 - Percent" to be the same format as the other % headings. Import it or copy it using 'Enter Data.'
Paste my code into the advanced editor after the "source = " line.
Debug the errors and follow what the algorithm does. Once it's working and you follow it, use the same ideas on your real data.
or
2) Copy everything from my code (apart from the "source =" line) and paste it in to your code (in the advanced editor). Delete the "in Source" line from your code. You will immediately get errors. You then have to debug each error (which will be based on column names or data layout). Go from step to step making each one work