Power Query Transformation Help

[VegaOne16]

Hello,

I am able to load the following information into the data module in power query:

date	time	timezone	Plant	Code	Description	Quantity	UoM	P/C/U/F
1/15/2022	12:00:00 AM	PST	Z999	1	A	1,206.63	MM	P
1/15/2022	12:00:00 AM	PST	Z999	2	B	-51.96	MM	P
1/15/2022	12:00:00 AM	PST	Z999	3	C	289.85	MM	P
1/15/2022	12:00:00 AM	PST	Z999	4	D	6,115.49	MM	P
1/15/2022	12:00:00 AM	PST	Z999	5	E	-165.91	MM	P
1/15/2022	12:00:00 AM	PST	Z999	6	F	5,988.26	MM	P
1/15/2022	12:00:00 AM	PST	Z999	7	G	1,307.79	MM	P
1/15/2022	12:00:00 AM	PST	Z999	8	H	410.18	MM	P
1/15/2022	12:00:00 AM	PST	Z999	9	I	0.33	MM	P
1/15/2022	12:00:00 AM	PST	Z999	10	J	6,896.93	MM	P
1/15/2022	12:00:00 AM	PST	Z999	11	K	1,555.67	MM	P
1/15/2022	12:00:00 AM	PST	Z999	12	L	2,406.54	MM	P
1/15/2022	12:00:00 AM	PST	Z999	13	M	1,867.28	MM	P
1/15/2022	12:00:00 AM	PST	Z999	14	N	4,001.44	MM	P
1/15/2022	12:00:00 AM	PST	Z999	15	O	8,248.26	MM	P
1/15/2022	12:00:00 AM	PST	Z999	16	P	40,151.23	MM	C
1/15/2022	12:00:00 AM	PST	Z999	17	Q	1,555.67	MM	F
1/15/2022	12:00:00 AM	PST	Z999	18	R	-	MM	P

I'm wanting to automatically transform the data as follows:

Could someone help me with the code to automatically make these changes in my query via the editor, along with a step-by-step in PQEditor on how to add the code in.

These are the steps that I am manually performing:

• If [Quantity] is "-" then delete the row.
• If [P/C/U/F] is "P" AND [Quantity] is negative, then change "P" to "C", and then change quantity from negative to positive.

Thank you for your assistance.

 

[KRice]

Click anywhere in your source table and convert it to a formal Excel table with Ctrl-t and indicate that the table has headers. While still somewhere on the table, launch Power Query (one way is from the menu bar...select Data > From Table/Range). Once PQ launches, you should see your table loaded into PQ. Launch the Advanced Editor (menu bar in PQ... View > Advanced Editor) and paste the code below (you may need to adjust the Source line...basically keeping the initial Source line already there). These steps are somewhat kludgy (there are probably better ways to do this but I ran into some issues), but they perform the steps you described. Once editing is done in the Advanced Editor, confirm with Done and then, if you'd like, you can step through the Applied Steps on the right-side window to see what each step does. Confirm at the last step that the table looks as you wish and then load in back into Excel using File > Close & Load.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Quantity] <> "-")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Quantity", type number}, {"date", type date}, {"time", type time}, {"timezone", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Check", each if [#"P/C/U/F"] = "P" and [Quantity] < 0 then "change" else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Abs.Qty", each Number.Abs([Quantity]), type number),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom", each [#"P/C/U/F"], each if [Check] = "change" then "C" else [#"P/C/U/F"],Replacer.ReplaceText,{"P/C/U/F"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Custom", each if [Check]="change" then [Abs.Qty] else [Quantity]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Quantity", "Check", "Abs.Qty"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"date", "time", "timezone", "Plant", "Code", "Description", "Custom", "UoM", "P/C/U/F"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Quantity"}})
in
    #"Renamed Columns"