I have imported a table into Power BI like shown below: The data comes from a CRM application.Record No.Project AmountContract Start DateMilestone #1 - # of DaysMilestone 1 - PercentMilestone #2 - # of DaysMilestone #2 - % of $Milestone #3 - # of DaysMilestone #3 - % of $Milestone #4 - # of DaysMilestone #4 - % of $Milestone #5 - # of DaysMilestone #5 - % of $16360000004/1/20200203020602090201202014851401/1/202003090301203015010 1560683410/1/201903090301203015010 I need the data formatted as follows:Record No.Project AmountContract Start DateMilestone# of DaysPercent1636000004/1/20201020%1636000004/1/202023020%1636000004/1/202036020%1636000004/1/202049020%1636000004/1/2020512020%14851401/1/20201030%14851401/1/202029030%14851401/1/2020312030%14851401/1/2020415010%1560683410/1/20191030%1560683410/1/201929030%1560683410/1/2019312030%1560683410/1/2019415010% 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!

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	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!

Need Help

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)

6 Replies

HotChilli

Community Champion

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)

rsbin
Community Champion
6 years ago
Thank you HotChilli.

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

Thanks again.
rsbin
Community Champion
6 years ago
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
  Community Champion
  6 years ago
  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,

dax

Community 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.

Forum Discussion

Power Query Transform Fields into Records

6 Replies

Related content

Table Transformation

Transform columns / type

Table Transformation

Data transform using power query

Merge rows in one and transform field