Power Query Transform Fields into Records

[rsbin]

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	Milestone #2 - # of Days	Milestone #2 - % of $	Milestone #3 - # of Days	Milestone #3 - % of $	Milestone #4 - # of Days	Milestone #4 - % of $	Milestone #5 - # of Days	Milestone #5 - % of $
163	6000000	4/1/2020	0	20	30	20	60	20	90	20	120	20
14	85140	1/1/2020	0	30	90	30	120	30	150	10
15	606834	10/1/2019	0	30	90	30	120	30	150	10

I need the data formatted as follows:

Record No.	Project Amount	Contract Start Date	Milestone	# of Days	Percent
163	600000	4/1/2020	1	0	20%
163	600000	4/1/2020	2	30	20%
163	600000	4/1/2020	3	60	20%
163	600000	4/1/2020	4	90	20%
163	600000	4/1/2020	5	120	20%
14	85140	1/1/2020	1	0	30%
14	85140	1/1/2020	2	90	30%
14	85140	1/1/2020	3	120	30%
14	85140	1/1/2020	4	150	10%
15	606834	10/1/2019	1	0	30%
15	606834	10/1/2019	2	90	30%
15	606834	10/1/2019	3	120	30%
15	606834	10/1/2019	4	150	10%

I am hoping one of you Power Query experts out there can provide some guidance.  Each record can have anywhere from 1 to 6 Milestones.  I have shown just a select sample.  Once I have it in this format, I can add Days to Contract Start and calculate the Revenue for the Month.  I started down the path of creating a separate Table for each Milestone, I thought I would reach out to see if there might be a cleaner and elegant solution.

Much thanks and appreciation for your guidance and support!

[dax]

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.

[HotChilli · Accepted solution]

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)

[rsbin]

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,

[rsbin]

Hi @HotChilli 

Great News!  Got it to work.   Thanks so much for the guidance and helpful advice.

Stay safe wherever you may be!

Kindest 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

[rsbin]

Thank you @HotChilli.

I will give this a shot and let you know I make out.

Thanks again.