Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Custom Sort and Delete Rows

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

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

 

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?

 

desktop.png

 

 

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

 

LivioLanzo
Solution Sage
Solution Sage

Hello @Anonymous

 

what is the logic behind? 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.