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
admin11
Memorable Member
Memorable Member

PQ how to remove Customer/Vendor Code= CES00001-SGD

Hi All

 

“Customer/Vendor Code” field name , the value label = CES00001-SGD

 

I need to remove above row using power query.

 

Can some one share with me how to do it ?


remark :- so that this row sales amount will not take in as sales. 

Paul

 

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

Have you put comma before the the ending of the line before? as in at the end of #"Renamed Columns"?

View solution in original post

12 REPLIES 12
PC2790
Community Champion
Community Champion

Hey @admin11 ,

 

You can make use of Table.SelectRows. The power Query code would look something like:

= Table.SelectRows
    (
        Source,
        (row)=> not(row[Customer/Vendor Code]="CES00001-SGD")
    )

See if it works out for you

@PC2790 

Thank you very much for sharing . Can you please take a screen shot , so that I know how to start and paste your code to PQ 

PC2790
Community Champion
Community Champion

Attaching a reference file for better understanding.

Please note I am using a different dataset and selecting the rows haing Material as B and not A

= Table.SelectRows
    (
        Source,
        (row)=> not(row[Material]="A")
    )

You can mould it as per your data

@PC2790 

Now i understand , you add script at advanced editor . 

 

Below is my script :-

 

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FS_SI/", [ApiVersion = 15]),
#"Filtered Hidden Files2" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (25)", each #"Transform File (25)"([Content])),
#"Renamed Columns3" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns3", {"Source.Name", "Transform File (25)"}),
#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File (25)", Table.ColumnNames(#"Transform File (25)"(#"Sample File (24)"))),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table Column2",{{"Source.Name", type text}, {"Customer/Vendor", type text}, {"Customer Name", type text}, {"Payment Terms", type text}, {"AR Invoice Number", Int64.Type}, {"AR Invoice Date", type text}, {"Month", type text}, {"Item No.", type text}, {"Item/Service Description", type text}, {"Quantity", Int64.Type}, {"Currency Rate", Int64.Type}, {"Price Currency", type text}, {"Price", type number}, {"Row Total (FC)", Int64.Type}, {"Row Total (SGD)", type number}, {"Row Total w/ VAT", type number}, {"ItemCost (SGD)", type number}, {"Total Item Cost Value (SGD)", type number}, {"SO Number", type any}, {"PO Number", type any}, {"Item Group", type text}, {"Product Code", type text}, {"Product Class", type any}, {"Manufacturer (Brand)", type text}, {"SlpName", type text}, {"Industry Description", type text}, {"Sub-Industry", type text}, {"Bill To Country Code", type text}, {"Ship To Country Code", type text}, {"Foreign Name", type text}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Changed Type2", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (16)", each #"Transform File (16)"([Content])),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Invoke Custom Function1", {{"AR Invoice Date", type date}}, "en-US"),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Foreign Name", "Description SAP"}, {"Manufacturer (Brand)", "BRAND_SAP"}, {"Item No.", "STOCK_CODE"}, {"Product Class", "PRODUCT_CLASS"}, {"ItemCost (SGD)", "COST_SAP"}, {"Customer Name", "COMPANY_NAME"}, {"AR Invoice Date", "INVOICE_DATE"}, {"Total Item Cost Value (SGD)", "COS"}, {"Row Total (SGD)", "SALES_SAP"}})
in
#"Renamed Columns"

 

May i know where should i insert below script you recommend to above code ?

 

= Table.SelectRows
    (
        Source,
        (row)=> not(row[Customer/Vendor]="CES00001-SGD")
    )

 

Paul

PC2790
Community Champion
Community Champion

Try this:

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FS_SI/", [ApiVersion = 15]),
#"Filtered Hidden Files2" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

#"FilteredRowsforCustomer" = Table.SelectRows
(
#"Filtered Hidden Files2",
(row)=> not(row[Customer/Vendor]="CES00001-SGD")
)
#"Invoke Custom Function2" = Table.AddColumn(#"FilteredRowsforCustomer", "Transform File (25)", each #"Transform File (25)"([Content])),
#"Renamed Columns3" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),

.

.

.

.

 

If this doesn't work,you can try:

#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table Column2",{{"Source.Name", type text}, {"Customer/Vendor", type text}, {"Customer Name", type text}, {"Payment Terms", type text}, {"AR Invoice Number", Int64.Type}, {"AR Invoice Date", type text}, {"Month", type text}, {"Item No.", type text}, {"Item/Service Description", type text}, {"Quantity", Int64.Type}, {"Currency Rate", Int64.Type}, {"Price Currency", type text}, {"Price", type number}, {"Row Total (FC)", Int64.Type}, {"Row Total (SGD)", type number}, {"Row Total w/ VAT", type number}, {"ItemCost (SGD)", type number}, {"Total Item Cost Value (SGD)", type number}, {"SO Number", type any}, {"PO Number", type any}, {"Item Group", type text}, {"Product Code", type text}, {"Product Class", type any}, {"Manufacturer (Brand)", type text}, {"SlpName", type text}, {"Industry Description", type text}, {"Sub-Industry", type text}, {"Bill To Country Code", type text}, {"Ship To Country Code", type text}, {"Foreign Name", type text}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Changed Type2", each [Attributes]?[Hidden]? <> true),
#"FilteredCustomerRows" = Table.SelectRows
    (
        #"Changed Type2",
        (row)=> not(row[Customer/Vendor]="CES00001-SGD")
    )
#"Invoke Custom Function1" = Table.AddColumn(#"FilteredCustomerRows", "Transform File (16)", each #"Transform File (16)"([Content])),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Invoke Custom Function1", {{"AR Invoice Date", type date}}, "en-US"),

@PC2790 

Thank you for your sharing again. i try both proposal by you , both i get the same error , it cannot detect field = Customer/Vendor

 

admin11_0-1658907411164.png

 

PC2790
Community Champion
Community Champion

Can you share the snaphot of the step-#"Changed Type2"?

Also try this once:

#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table Column2",{{"Source.Name", type text}, {"Customer/Vendor", type text}, {"Customer Name", type text}, {"Payment Terms", type text}, {"AR Invoice Number", Int64.Type}, {"AR Invoice Date", type text}, {"Month", type text}, {"Item No.", type text}, {"Item/Service Description", type text}, {"Quantity", Int64.Type}, {"Currency Rate", Int64.Type}, {"Price Currency", type text}, {"Price", type number}, {"Row Total (FC)", Int64.Type}, {"Row Total (SGD)", type number}, {"Row Total w/ VAT", type number}, {"ItemCost (SGD)", type number}, {"Total Item Cost Value (SGD)", type number}, {"SO Number", type any}, {"PO Number", type any}, {"Item Group", type text}, {"Product Code", type text}, {"Product Class", type any}, {"Manufacturer (Brand)", type text}, {"SlpName", type text}, {"Industry Description", type text}, {"Sub-Industry", type text}, {"Bill To Country Code", type text}, {"Ship To Country Code", type text}, {"Foreign Name", type text}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Changed Type2", each [Attributes]?[Hidden]? <> true),
#"FilteredCustomerRows" = Table.SelectRows
    (
        #"Filtered Hidden Files1",
        (row)=> not(row[Customer/Vendor]="CES00001-SGD")
    ),
#"Invoke Custom Function1" = Table.AddColumn(#"FilteredCustomerRows", "Transform File (16)", each #"Transform File (16)"([Content])),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Invoke Custom Function1", {{"AR Invoice Date", type date}}, "en-US"),

@PC2790 

 

below link is my raw data , I need to remove one row. As I mentioned previously. 

https://www.dropbox.com/s/4sqobepr4s8mpai/SI_TD.csv?dl=0

 

PC2790
Community Champion
Community Champion

Yes, that helps.

This should work

= Table.SelectRows
    (
        #"Changed Type",
        (row)=> not(row[#"Customer/Vendor"]="CES00001-SGD")
    )

Sample file as attached.

@PC2790 

I still get error msg , after view the PBI you share.

admin11_0-1658993972051.png

( part A )This is your attach PBI script , which you import my raw data to your C drive :-

 

let
Source = Csv.Document(File.Contents("C:\Users\pankhari.chawla\Downloads\Book1.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}, {"Column1.17", type number}, {"Column1.18", Int64.Type}, {"Column1.19", type text}, {"Column1.20", type text}, {"Column1.21", type text}, {"Column1.22", type text}, {"Column1.23", type text}, {"Column1.24", type text}, {"Column1.25", type text}, {"Column1.26", type text}, {"Column1.27", type text}, {"Column1.28", type text}, {"Column1.29", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer/Vendor", type text}, {"Customer Name", type text}, {"Payment Terms", type text}, {"AR Invoice Number", Int64.Type}, {"AR Invoice Date", type date}, {"Month", type text}, {"Item No.", type text}, {"Item/Service Description", type text}, {"Quantity", Int64.Type}, {"Currency Rate", type number}, {"Price Currency", type text}, {"Price", type number}, {"Row Total (FC)", type number}, {"Row Total (SGD)", type number}, {"Row Total w/ VAT", type number}, {"ItemCost (SGD)", type number}, {"Column17", type number}, {"Column18", Int64.Type}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"", type text}, {"_1", type text}, {"_2", type text}}),
#"Filtered Rows"= Table.SelectRows
(
#"Changed Type2",
(row)=> not(row[#"Customer/Vendor"]="CES00001-SGD")
)
in
#"Filtered Rows"


( part B )Below is my PBI org code :-

 

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FS_SI/", [ApiVersion = 15]),
#"Filtered Hidden Files2" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (25)", each #"Transform File (25)"([Content])),
#"Renamed Columns3" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns3", {"Source.Name", "Transform File (25)"}),
#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File (25)", Table.ColumnNames(#"Transform File (25)"(#"Sample File (24)"))),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table Column2",{{"Source.Name", type text}, {"Customer/Vendor", type text}, {"Customer Name", type text}, {"Payment Terms", type text}, {"AR Invoice Number", Int64.Type}, {"AR Invoice Date", type text}, {"Month", type text}, {"Item No.", type text}, {"Item/Service Description", type text}, {"Quantity", Int64.Type}, {"Currency Rate", Int64.Type}, {"Price Currency", type text}, {"Price", type number}, {"Row Total (FC)", Int64.Type}, {"Row Total (SGD)", type number}, {"Row Total w/ VAT", type number}, {"ItemCost (SGD)", type number}, {"Total Item Cost Value (SGD)", type number}, {"SO Number", type any}, {"PO Number", type any}, {"Item Group", type text}, {"Product Code", type text}, {"Product Class", type any}, {"Manufacturer (Brand)", type text}, {"SlpName", type text}, {"Industry Description", type text}, {"Sub-Industry", type text}, {"Bill To Country Code", type text}, {"Ship To Country Code", type text}, {"Foreign Name", type text}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Changed Type2", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (16)", each #"Transform File (16)"([Content])),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Invoke Custom Function1", {{"AR Invoice Date", type date}}, "en-US"),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Foreign Name", "Description SAP"}, {"Manufacturer (Brand)", "BRAND_SAP"}, {"Item No.", "STOCK_CODE"}, {"Product Class", "PRODUCT_CLASS"}, {"ItemCost (SGD)", "COST_SAP"}, {"Customer Name", "COMPANY_NAME"}, {"AR Invoice Date", "INVOICE_DATE"}, {"Total Item Cost Value (SGD)", "COS"}, {"Row Total (SGD)", "SALES_SAP"}})
in
#"Renamed Columns"

 

( part C )Base on your code ( part A ) , i try to

 

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FS_SI/", [ApiVersion = 15]),
#"Filtered Hidden Files2" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (25)", each #"Transform File (25)"([Content])),
#"Renamed Columns3" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns3", {"Source.Name", "Transform File (25)"}),
#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File (25)", Table.ColumnNames(#"Transform File (25)"(#"Sample File (24)"))),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table Column2",{{"Source.Name", type text}, {"Customer/Vendor", type text}, {"Customer Name", type text}, {"Payment Terms", type text}, {"AR Invoice Number", Int64.Type}, {"AR Invoice Date", type text}, {"Month", type text}, {"Item No.", type text}, {"Item/Service Description", type text}, {"Quantity", Int64.Type}, {"Currency Rate", Int64.Type}, {"Price Currency", type text}, {"Price", type number}, {"Row Total (FC)", Int64.Type}, {"Row Total (SGD)", type number}, {"Row Total w/ VAT", type number}, {"ItemCost (SGD)", type number}, {"Total Item Cost Value (SGD)", type number}, {"SO Number", type any}, {"PO Number", type any}, {"Item Group", type text}, {"Product Code", type text}, {"Product Class", type any}, {"Manufacturer (Brand)", type text}, {"SlpName", type text}, {"Industry Description", type text}, {"Sub-Industry", type text}, {"Bill To Country Code", type text}, {"Ship To Country Code", type text}, {"Foreign Name", type text}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Changed Type2", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (16)", each #"Transform File (16)"([Content])),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Invoke Custom Function1", {{"AR Invoice Date", type date}}, "en-US"),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Foreign Name", "Description SAP"}, {"Manufacturer (Brand)", "BRAND_SAP"}, {"Item No.", "STOCK_CODE"}, {"Product Class", "PRODUCT_CLASS"}, {"ItemCost (SGD)", "COST_SAP"}, {"Customer Name", "COMPANY_NAME"}, {"AR Invoice Date", "INVOICE_DATE"}, {"Total Item Cost Value (SGD)", "COS"}, {"Row Total (SGD)", "SALES_SAP"}})

#"Filtered Rows"= Table.SelectRows
(
#"Changed Type2",
(row)=> not(row[#"Customer/Vendor"]="CES00001-SGD")
)

in
#"Renamed Columns"

 

Paul

 

PC2790
Community Champion
Community Champion

Have you put comma before the the ending of the line before? as in at the end of #"Renamed Columns"?

@PC2790 

Thank you for help me spot the mistake . after add comma , now the error dis-appear. But when i try to refresh the data , i notice that SALES_I now when loading , it have a lot of error. and i notice that the it does not remove the row with customer/vendor with CES.

admin11_0-1659018005677.png

Since the extra row , the amount is very small amount.  i think i better don't remove it. now i am happy to aware that PQ can using editor to filter certain row. 

i think my issue SALES_I need to append ith SALES. that is the reasons got error. 

 

Thank you for your help.

 

Paul

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.