Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Communities,
Please see below,I have used power query to sort it out columns and delete but it did not generate correct result.
Can you anyone please suggest me How to do in Power Query ?
BEFORE | |||||||
Order | Invoice | Flag | Units | Average Rate | Current Plan Rate | Price list Name | Price List |
455 | 110277 | N | 1 | 265499.92 | 265499.92 | ASX | 433262 |
455 | 110277.1 | Y | -1 | -265499.92 | -265499.92 | null | 0 |
455 | 110277.2 | Y | 1 | 265499.92 | 265499.92 | null | 0 |
455 | 110277 | N | 1 | 49706.04 | 49706.04 | ASX | 83259 |
455 | 110277.1 | Y | -1 | -49706.04 | -49706.04 | null | 0 |
455 | 110277.2 | Y | 1 | 49706.04 | 49706.04 | null | 0 |
AFTER | |||||||
Order | Invoice | Flag | Units | Average Rate | Current Plan Rate | Price list Name | Price List |
455 | 110277.2 | Y | 1 | 265499.92 | 265499.92 | null | 0 |
455 | 110277.2 | Y | 1 | 49706.04 | 49706.04 | null | 0 |
Solved! Go to Solution.
@Anonymous,
Please add a blank query in Power BI Desktop and paste the following code into Advanced Editor of the query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1VdJRMjQ0MDI3BzL8QBwgNjIzNbG01LM0QmM7BkcASRNjYyMzI6VYHVT9eiCdkUCsC2LoIutD4eSV5uQAKQNMA4ygBuBzAk7dSM43sTQ3MNMzMEFlQhxvYWxkaknI7Ui6kNnEuRy77Qi9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, Invoice = _t, Flag = _t, #" Units" = _t, #" Average Rate" = _t, #"Current Plan Rate" = _t, #"Price list Name" = _t, #"Price List" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Invoice", type number}, {"Flag", type text}, {" Units", Int64.Type}, {" Average Rate", type number}, {"Current Plan Rate", type number}, {"Price list Name", type text}, {"Price List", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "NewInvoice", each if Text.End(Number.ToText([Invoice]),2)=".1" or Text.End(Number.ToText([Invoice]),2)=".2" then Number.ToText([Invoice]) else Number.ToText([Invoice])&".1"), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"NewInvoice", type number}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "NewUnit", each if Text.End(Number.ToText([NewInvoice]),2)=".1" and [#" Units"]<0 then Number.Abs([#" Units"]) else [#" Units"]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NewAverageRate", each if Text.End(Number.ToText([NewInvoice]),2)=".1" and [#" Average Rate"]<0 then Number.Abs([#" Average Rate"]) else [#" Average Rate"]), #"Removed Duplicates" = Table.Distinct(#"Added Custom2", {"NewAverageRate", "NewUnit", "NewInvoice", "Order"}), #"Added Custom3" = Table.AddColumn(#"Removed Duplicates", "Custom", each if [Invoice]<>[NewInvoice] then 0 else 1), #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"NewInvoice", "NewUnit", "NewAverageRate", "Custom"}) in #"Removed Columns"
Regards,
Lydia
@Anonymous,
Please add a blank query in Power BI Desktop and paste the following code into Advanced Editor of the query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1VdJRMjQ0MDI3BzL8QBwgNjIzNbG01LM0QmM7BkcASRNjYyMzI6VYHVT9eiCdkUCsC2LoIutD4eSV5uQAKQNMA4ygBuBzAk7dSM43sTQ3MNMzMEFlQhxvYWxkaknI7Ui6kNnEuRy77Qi9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, Invoice = _t, Flag = _t, #" Units" = _t, #" Average Rate" = _t, #"Current Plan Rate" = _t, #"Price list Name" = _t, #"Price List" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Invoice", type number}, {"Flag", type text}, {" Units", Int64.Type}, {" Average Rate", type number}, {"Current Plan Rate", type number}, {"Price list Name", type text}, {"Price List", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "NewInvoice", each if Text.End(Number.ToText([Invoice]),2)=".1" or Text.End(Number.ToText([Invoice]),2)=".2" then Number.ToText([Invoice]) else Number.ToText([Invoice])&".1"), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"NewInvoice", type number}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "NewUnit", each if Text.End(Number.ToText([NewInvoice]),2)=".1" and [#" Units"]<0 then Number.Abs([#" Units"]) else [#" Units"]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NewAverageRate", each if Text.End(Number.ToText([NewInvoice]),2)=".1" and [#" Average Rate"]<0 then Number.Abs([#" Average Rate"]) else [#" Average Rate"]), #"Removed Duplicates" = Table.Distinct(#"Added Custom2", {"NewAverageRate", "NewUnit", "NewInvoice", "Order"}), #"Added Custom3" = Table.AddColumn(#"Removed Duplicates", "Custom", each if [Invoice]<>[NewInvoice] then 0 else 1), #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"NewInvoice", "NewUnit", "NewAverageRate", "Custom"}) in #"Removed Columns"
Regards,
Lydia
Hi Lydia,
Sorry to bother you again,Above script is working fine for most of the scenarios but in one scenarios I have 1 Invoice number(#110276) and that invoice does not have any corresponding or matching invoice (Example doest not have any extension like .1,.2,.3 etc).
<if invoice no is unique we need to keep it and if not unique then do all kind of transformation>
Please see below,Add Conditional Column when condition satisfied it removes Invoice #110276.
Can you please suggest me any workaround?
Order | Invoice | Flag | Units | Average Rate | Current Plan Rate | Price list Name | Price List | Status |
455 | 110277 | N | 1 | 265499.92 | 265499.92 | ASX | 433262 | Avaliable |
455 | 110277.1 | Y | -1 | -265499.92 | -265499.92 | null | 0 | Delete |
455 | 110277.2 | Y | 1 | 265499.92 | 265499.92 | null | 0 | Avaliable |
455 | 110277 | N | 1 | 49706.04 | 49706.04 | ASX | 83259 | Avaliable |
455 | 110277.1 | Y | -1 | -49706.04 | -49706.04 | null | 0 | Delete |
455 | 110277.2 | Y | 1 | 49706.04 | 49706.04 | null | 0 | Avaliable |
455 | 110277.1 | Y | 1 | 5000.92 | 5000.92 | null | 0 | Avaliable |
455 | 110279.1 | Y | 1 | -265499.92 | -265499.92 | null | 0 | Avaliable |
455 | 110276 | N | 1 | 265499.92 | 265499.92 | null | 0 | Avaliable |
Hello @Anonymous
what is the logic behind?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi Livio,
Please see below,
Logic A.Keep all the invoices rows ending with .2
Logic B: If Invoice has .1 then i need to find same invoice number without .1 and check units (1 & -1) or Current Plan Rate (Postivie and Negative) and if there has a match i need to delete those 2 rows.
Logic C:if invoices ending without .1 need to keep it but if there has matching invoices with .1 then,rows needs to be delte.
let me knof if you need more clarifications.
Thanks
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |