cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jinoh
New Member

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?

 

Question 1.PNGQuestion 2.PNG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors