Hello,
When I change my power bi from a pbix to a pbit I get an error for renaming my columns and I'm not quite sure how to handle this. Below is my advance editor query.
let
Source = Sql.Database("cp1b-vm04", "lme_1710", [Query="SELECT DISTINCT#(lf) orders.salesperson_id salesperson_id,#(lf) orders.status,#(lf) orders.commissions_exempt,#(lf) orders.commission_paid_date,#(lf) orders.id orderid,#(lf) orders.customer_id customer_id,#(lf) orders.commission_paid commission_paid,#(lf) destination.actual_arrival actual_arrival,#(lf) customer.id cust_no,#(lf) customer.name customer_name,#(lf) origin.city_name origin_city_name,#(lf) origin.state origin_state,#(lf) destination.city_name destination_city_name, #(lf) destination.state destination_state,#(lf) orders.percentage percentage,#(lf) orders.bill_date bill_date,#(lf) orders.commissions_exempt commissions_exempt,#(lf) orders.zero_balance_date zero_balance_date,#(lf) orders.total_charge total_charge,#(lf) orders.total_margin_amt total_margin_amt,#(lf) orders.commission_due_amt commission_due_amt,#(lf) orders.total_movement_pay total_movement_pay,#(lf) orders.override_pay_amt override_pay_amt,#(lf) orders.override_total_charge override_total_charge,#(lf) orders.current_ar_balance current_ar_balance,#(lf) orders.commission_paid_date,#(lf) orders.salesperson_id2,#(lf) orders.percentage2,#(lf) orders.commission_due_amt2 commission_due_amt2,#(lf) salesperson.name#(lf)FROM orders#(lf)LEFT OUTER JOIN stop origin#(lf) ON origin.id = orders.shipper_stop_id#(lf) AND origin.company_id = 'TMS2'#(lf)LEFT OUTER JOIN stop destination#(lf) ON destination.id = orders.consignee_stop_id#(lf) AND destination.company_id = 'TMS2'#(lf)LEFT OUTER JOIN customer#(lf) ON customer.id = orders.customer_id#(lf) AND customer.company_id = 'TMS2'#(lf)LEFT OUTER JOIN stop consignee_stop#(lf) ON consignee_stop_id = consignee_stop.id#(lf) AND consignee_stop.company_id = 'TMS2'#(lf)LEFT OUTER JOIN freight_group#(lf) ON orders.id = freight_group.lme_order_id#(lf) AND freight_group.company_id = 'TMS2'#(lf)LEFT OUTER JOIN salesperson #(lf) ON salesperson.id = orders.salesperson_id#(lf) AND salesperson.company_id = 'TMS2'#(lf) WHERE #(lf)orders.company_id = 'TMS2'"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([salesperson_id] = sp or [salesperson_id2] = sp) and [commissions_exempt] <> "Y" and [status] <> "V" and ([commission_paid_date] <> null) and([commission_paid_date] <= PaidDate and [commission_paid_date] >= Date.AddDays(PaidDate, -13)) or ([commission_paid_date] = null)
and ([salesperson_id] =sp or [salesperson_id2] = sp) and DateTime.LocalNow() < PaidDate and ([zero_balance_date] <= PaidDate and [zero_balance_date] >= Date.AddDays(PaidDate, -13))),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Commission", each if [salesperson_id2] = sp then [commission_due_amt2] else [commission_due_amt]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "CommRate", each if [salesperson_id] = sp then [percentage] else [percentage2]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Commission", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"commission_paid_date", "Date"}, {"orderid", "Order Id"}, {"origin_state", "Origin"}, {"destination_state", "Destination"}, {"total_margin_amt", "Profit"}})
in
#"Renamed Columns"
