Forum Discussion
How to add functions in advanced editor(Power Query)
I'm a newcomer for Power Query and I'm wondering how to add some functions in Power Query Advanced editor
Here's my current advanced editor function
let
Source = Table.Combine({#"Amazon May item discount Item O Ship X", #"Amazon May item discount Item O Ship O", #"Amazon May item discount Item X Ship O", #"Amazon May item discount Item X Ship X"}),
#"Replaced Value" = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,{"promotion-ids.3"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"amazon-order-id", "purchase-date", "sales-channel", "sku", "quantity", "currency", "item-price", "item-promotion-discount", "promotion-ids1", "promotion-ids.2", "promotion-ids.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{<!-- -->{"promotion-ids1", "promotion-ids.1"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "sku", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"sku.1", "sku.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{<!-- -->{"sku.1", type text}, {"sku.2", type text}, {"promotion-ids.2", type text}, {"promotion-ids.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"sku.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "sku.1", Splitter.SplitTextByDelimiter("&", QuoteStyle.Csv), {"sku.1.1", "sku.1.2", "sku.1.3", "sku.1.4", "sku.1.5", "sku.1.6"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{<!-- -->{"sku.1.1", type text}, {"sku.1.2", type text}, {"sku.1.3", type text}, {"sku.1.4", type text}, {"sku.1.5", type text}, {"sku.1.6", type text}})
#
in
#"Changed Type1"
And I want to add this function below
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
shiftedList = {null} & List.RemoveLastN(Table.Column(Source,"promotion-ids.1"),1),
custom1 = Table.ToColumns(Source) & {shiftedList},
custom2 = Table.FromColumns(custom1,Table.ColumnNames(Source) & {"Prev"}),
x=Table.ReplaceValue(custom2, each [#"promotion-ids.1"], each if [Prev]=[#"promotion-ids.1"] then null else[#"promotion-ids.1"] ,Replacer.ReplaceValue,{"promotion-ids.1"}),
"Removed Columns" = Table.RemoveColumns(x,{"Prev"})
in #"Removed Columns"
The current table name is "Amazon May item discount integration" and where I'm willing to make changes is a column named "promotion-ids.1"
Attached are screenshots of Current tables and advanced editor.
I tried to paste the added functions but it said there's an error with it.
Can anybody help me solve this problem?
Jinoh Here is the official documentation on using custom functions with Power Query.
Using custom functions in Power Query - Power Query | Microsoft Docs
Custom functions have a slightly different syntax than queries as they are designed to ingest parameters.
let Source = () => let Source = "" in Source in Source
1 Reply
- Greg_DecklerCommunity Champion
Jinoh Here is the official documentation on using custom functions with Power Query.
Using custom functions in Power Query - Power Query | Microsoft Docs
Custom functions have a slightly different syntax than queries as they are designed to ingest parameters.
let Source = () => let Source = "" in Source in Source