Power Query - Custom Column Logic for Multiple Queries

johnny51981

Active Member
Joined
Jun 8, 2015
Messages
371
I have 2 data sources that come from the same application, but one is for Sales and one is for Invoicing. The columns are all named and formatted the same.

There is a Custom Column in queries that use those 2 data sources where the logic is to be exactly the same...but sometimes out of sight out of mind and I end up not tying out.

Is there a way to create the Custom Column in a single location for editing purposes, but is used in more than one query?

Let's just say, for very very simple example's sake, that my columns are Job Number, Line Item Type, Status and logic is
if [Line Item Type] <> "Progressive" and [Status] = "Approved" then "Yes" else "No"
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If you add the following code below, you need to invoke the custom function, and then reference the last step instead of the table name.

Power Query:
(tblName as table) =>
let
    Source = Table.AddColumn(tblName, "Custom", each if [Line Item Type] <> "Progressive" and [Status] = "Approved" then "Yes" else "No")
in
    Source


Your next step would look something like (assuming you named your custom function and the previous step is called #"Changed Type"):

Power Query:
= Table.AddColumn(#"Changed Type", "Custom Column", each fnCustomColumn(#"Changed Type"))
 
Upvote 1
Solution
If you add the following code below, you need to invoke the custom function, and then reference the last step instead of the table name.

Power Query:
(tblName as table) =>
let
    Source = Table.AddColumn(tblName, "Custom", each if [Line Item Type] <> "Progressive" and [Status] = "Approved" then "Yes" else "No")
in
    Source


Your next step would look something like (assuming you named your custom function and the previous step is called #"Changed Type"):

Power Query:
= Table.AddColumn(#"Changed Type", "Custom Column", each fnCustomColumn(#"Changed Type"))
This looks to be the solution I was seeking. Appreciate it!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top