Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Have you put comma before the the ending of the line before? as in at the end of #"Renamed Columns"?
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
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
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
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"),
Thank you for your sharing again. i try both proposal by you , both i get the same error , it cannot detect field = Customer/Vendor
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"),
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
I still get error msg , after view the PBI you share.
( 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
Have you put comma before the the ending of the line before? as in at the end of #"Renamed Columns"?
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.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |