Pivot without creating duplicates and duplicate a row

[johndolan2]

Hi - Having no luck and hoping for help. I am trying to accomplish four things. The start point represents my current table. The end point is what I want the table to look like.

* Each unique service ID represents a single product.

What I am trying to accomplish

1. Pivot A LOCATION AND Z LOCATION.
2. Divide the "TRANSPORT"  SERVICE COMPONENT CHARGE between the A AND Z LOCAITONS.
3. Avoid creating duplicate rows.
4. Have power query editor repeat these steps every time my source spreadsheet is updated with additonal products.

Thanks, John

START POINT	PRODUCT	SERVICE ID	SERVICE COMPONENT NAME	SERVICE COMPONENT ID	SERVICE COMPONENT CHARGE	A LOCATION	Z LOCATION
	APPLE	1	A ACCESS	105	$100	BOSTON, MA	SEATTLE WA
	APPLE	1	Z ACCESS	1B42	$50	BOSTON, MA	SEATTLE WA
	APPLE	1	TRANSPORT	1C	$100	BOSTON, MA	SEATTLE WA
	CARROT	2	A ACCESS	54C	$20	MIAMI, FL
	CARROT	2	Z ACCESS	205BB	$0	MIAMI, FL
	CARROT	2	SPEED	45761	$50	MIAMI, FL
	CARROT	2	MAINT	688A	$25	MIAMI, FL

END POINT	PRODUCT	SERVICE ID	SERVICE COMPONENT NAME	SERVICE COMPONENT ID	SERVICE COMPONENT CHARGE	LOCATION ATTRIBUTE	LOCATION
	APPLE	1	A ACCESS	105	$100	A	BOSTON, MA
	APPLE	1	Z ACCESS	1B42	$50	Z	SEATTLE WA
	APPLE	1	TRANSPORT	1C	$50	A	BOSTON, MA
	APPLE	1	TRANSPORT	1C	$50	Z	SEATTLE WA
	CARROT	2	A ACCESS	54C	$20	A	MIAMI, FL
	CARROT	2	Z ACCESS	205BB	$0	Z
	CARROT	2	SPEED	45761	$50	A	MIAMI, FL
	CARROT	2	MAINT	688A	$25	A	MIAMI, FL

[Icey · Accepted solution]

Hi @johndolan2 ,

Please check:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9BCsMgEEWvItKlCxVNsx2tBUGjqFBoyN1ylp6sKoUmtIQsdOYv3uPPPGOI0RlMMKsPEGhtcm6RyvpfGKWoThVyCRNBHmrIBkpxBj0AL2QveG4ESvBmkF3wWs8qSoIpx5BKy/pkBw0phUbw/RVSdAP/dPAWvCXo7mr65TblOZVKNbKDx1iOxtzqFPI6sO/Fx5AHO7V9GEfoDeW/hssb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODUCT = _t, #"SERVICE ID" = _t, #"SERVICE COMPONENT NAME" = _t, #"SERVICE COMPONENT ID" = _t, #"SERVICE COMPONENT CHARGE" = _t, #"A LOCATION" = _t, #"Z LOCATION" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODUCT", type text}, {"SERVICE ID", Int64.Type}, {"SERVICE COMPONENT NAME", type text}, {"SERVICE COMPONENT ID", type text}, {"SERVICE COMPONENT CHARGE", Currency.Type}, {"A LOCATION", type text}, {"Z LOCATION", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","#(00A0)BOSTON, MA","BOSTON, MA",Replacer.ReplaceText,{"A LOCATION"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(00A0)MIAMI, FL","MIAMI, FL",Replacer.ReplaceText,{"A LOCATION"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"PRODUCT", "SERVICE ID", "SERVICE COMPONENT NAME", "SERVICE COMPONENT ID", "SERVICE COMPONENT CHARGE"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "LOCATION ATTRIBUTE"}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns"," LOCATION","",Replacer.ReplaceText,{"LOCATION ATTRIBUTE"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value2",{{"Value", "LOCATION"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "SERVICE COMPONENT NAME", "SERVICE COMPONENT NAME - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "SERVICE COMPONENT NAME - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"SERVICE COMPONENT NAME - Copy.1", "SERVICE COMPONENT NAME - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SERVICE COMPONENT NAME - Copy.1", type text}, {"SERVICE COMPONENT NAME - Copy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"SERVICE COMPONENT NAME - Copy.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if ( [#"SERVICE COMPONENT NAME - Copy.1"] = [SERVICE COMPONENT NAME] and [LOCATION] <> "" ) or ([#"SERVICE COMPONENT NAME - Copy.1"] = [LOCATION ATTRIBUTE] ) then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> null and [Custom] <> ""),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"SERVICE COMPONENT NAME - Copy.1", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"PRODUCT", "SERVICE ID", "SERVICE COMPONENT NAME", "SERVICE COMPONENT ID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [PRODUCT=nullable text, SERVICE ID=nullable number, SERVICE COMPONENT NAME=nullable text, SERVICE COMPONENT ID=nullable text, SERVICE COMPONENT CHARGE=nullable number, LOCATION ATTRIBUTE=nullable text, LOCATION=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"SERVICE COMPONENT CHARGE", "LOCATION ATTRIBUTE", "LOCATION"}, {"SERVICE COMPONENT CHARGE", "LOCATION ATTRIBUTE", "LOCATION"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded All", "Divide_SERVICE COMPONENT CHARGE", each if [SERVICE COMPONENT NAME] = "TRANSPORT" then [SERVICE COMPONENT CHARGE] / [Count] else [SERVICE COMPONENT CHARGE]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Count", "SERVICE COMPONENT CHARGE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"PRODUCT", "SERVICE ID", "SERVICE COMPONENT NAME", "SERVICE COMPONENT ID", "Divide_SERVICE COMPONENT CHARGE", "LOCATION ATTRIBUTE", "LOCATION"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Divide_SERVICE COMPONENT CHARGE", "SERVICE COMPONENT CHARGE"}})
in
    #"Renamed Columns2"

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@johndolan2 , Duplicate the table in power query. Option on right-click on a table or under manage.

From one table Delete A location and in another one delete Z location. (rename both as location) Remove duplicate from the table of Z Location (If needed delete duplicate from Table A location) : https://www.youtube.com/watch?v=Hc5bIXkpGVE

Append both tables into one table. : https://www.youtube.com/watch?v=KyXIDInZMxk

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[johndolan2]

Thank you very much Amitchandak. John

[VijayP]

@johndolan2 

I think the Data is not complete with regards to your question. Howerver Created a file with the same data

please do let me kn ow it helps!

https://drive.google.com/file/d/1xITK16r6wHzseRFWhfRmQd7O89OAUaTi/view?usp=sharing

[johndolan2]

Thanks very much Vijay. John