Power Query - Custom Column Logic for Multiple Queries

J

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"
Sort by date Sort by votes
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
ExcelToDAX said:
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"))
Click to expand...
This looks to be the solution I was seeking. Appreciate it!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
S
  • Question
Power Query looking for item not in list
Replies
1
Views
345
Matt Allington
M
J
  • Solved
Power Query - Logic Categorize and Group by
Replies
6
Views
486
jdutle
J
E
  • Question
Power Query to automate payroll entry
Replies
0
Views
219
egspen2
E
earthworm
  • Question
Exporting Data from Power Query into Multiple Sheet and merging steps into one.
Replies
3
Views
477
Matt Allington
M

Forum statistics

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

Share this page

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