Help Insert rows based on condition

[AnisAnalyst]

Hello community,

I d appreciate if someone can help with this: 

My aim is to split a row into few other rows based on a condition in power query 

If cell content in column "Unit charge" = "Equal Charge"

    then insert 8 new rows

    input the following names in the adjacent column to the right named "New Unit charge": "MITTE", "PARIS", AMSTERDAM", "BARCELONA", "LONDON", "BERLIN", "MIAMI", "MADRID".

     then split the amount that was originly in the main row named "Eqauly Charge" into 8 equal portions.

     Finally, once this is done, I would like to rpevent rows with the content "Eqaul charge" to split again. so may be change the name of the Eqaul charge to "Eqaul charge-split"

If cell content in column "Unit charge" <> "Equal Charge" then copy the value into the column "New Unit Charge".

I have attached a template that shows the original data and the desired transformation. 

 Please let me know if you can help.

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @AnisAnalyst,

Please create a new table: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcsxCoAwDEDRq0hmLxFNhkDTStqtdCgiOrgoeH/dzfofv1bg6+nnsB793jcYQaUUhjb+YEGT7AFqLmyE6uGENnNIET38OqXobmxBXFFBFReQTAhaewE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unit Charged" = _t, #"New Unit charge" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit Charged", type text}, {"New Unit charge", type text}})
in
    #"Changed Type"

Then in your orignal table, select columns from Jan-18 to Dec-18, then click Unpivot Coumns, create a new column to divide values by 8 if "Unit charge" = "Equal Charge", then pivot columns back. Merge the table with the new table based on Unit Charged columns. For detail information, please see attached pbix file. 

Assume table data like below: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotS80rTVXSgbEUQooSy1JzFFwrClLzilOLgTLB3kDCIxBIGBoYGIAoY1OqUEDDYnVIcIOvp6OvJ5A2BbuCUhK71QFF+WmpxcWZ+XmJOQpuqWDL/UMcQda6FpYCxZIzEovSQToMjYxNqUsRGxj+kVhcA40a6lGxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Department = _t, Item = _t, Description = _t, #"Unit Charged" = _t, #"18-Jan" = _t, #"Feb-18" = _t, #"Mar-18" = _t, #"Apr-18" = _t, #"May-18" = _t, #"Jun-18" = _t, #"Jul-18" = _t, #"Aug-18" = _t, #"Sep-18" = _t, #"Oct-18" = _t, #"Nov-18" = _t, #"Dec-18" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Item", type text}, {"Description", type text}, {"Unit Charged", type text}, {"18-Jan", Int64.Type}, {"Feb-18", Int64.Type}, {"Mar-18", Int64.Type}, {"Apr-18", Int64.Type}, {"May-18", Int64.Type}, {"Jun-18", Int64.Type}, {"Jul-18", Int64.Type}, {"Aug-18", Int64.Type}, {"Sep-18", Int64.Type}, {"Oct-18", Int64.Type}, {"Nov-18", Int64.Type}, {"Dec-18", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Department", "Item", "Description", "Unit Charged"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Unit Charged]="Equal charge"
then Value.Divide([Value],8) 
else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum),
    #"Merged Queries" = Table.NestedJoin(#"Pivoted Column",{"Unit Charged"},Table2,{"Unit Charged"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Unit Charged", "New Unit charge"}, {"Table2.Unit Charged", "Table2.New Unit charge"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if [Unit Charged]=[Table2.Unit Charged] then [Table2.New Unit charge] else [Unit Charged]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Description", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Table2.Unit Charged", "Table2.New Unit charge", "Unit Charged"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Department", "Item", "Description", "Custom", "18-Jan", "Feb-18", "Mar-18", "Apr-18", "May-18", "Jun-18", "Jul-18", "Aug-18", "Sep-18", "Oct-18", "Nov-18", "Dec-18"})
in
    #"Reordered Columns"

Best Regards,
Qiuyun Yu