Power Query Conditional Column

**MartinvanK** · 10-25-2020, 03:08 PM

Hi, I have question about Conditional Column, see attached screenprint
I want to see the following. Column Dosp05Lookup contains in most fields data but for the fields that contain no data (blanks) it should get its data from column CSORNADR1 where there is always data. So it would read, If Dosp05Lookup = empty then CSORNADR1 and if Dosp05Lookup is not empty then Dosp05Lookup.
But when I try this as shown in the attachment it does not work. The conditional column returns in case Dosp05Lookup is not empty the same data from Dosp05Lookup but when Dosp05Lookup is empty the conditional columns stays empty...

Kind regards,

Martin van Koningsbruggen

**alansidman** · 10-25-2020, 03:20 PM

In the Add a Column
Select Custom Column and type the following into the window.

Please Login or Register  to view this content.

Use the Null value instead of the empty quotes you would normally use in Native Excel.

**MartinvanK** · 10-25-2020, 03:56 PM

Hi,

I changed that but it still returns with empty cells. See print screen.

**alansidman** · 10-25-2020, 04:00 PM

Read the yellow banner at the top of this page. Cannot manipulate data in a picture. Really need to see the file in order to analyze and resolve the issue.

**MartinvanK** · 10-25-2020, 04:20 PM

I had to delete a lot of data, I hope it is sufficient to show how it works. It is the Query at the DOSP06 New tab

**alansidman** · 10-25-2020, 04:28 PM

Cannot review or change the line of code as you have the source data in another file that I don't have access to.

However, I believe that you need to change your Mcode for the line in question to:

Please Login or Register  to view this content.

Without access to your file, I could not test for accuracy

**MartinvanK** · 10-26-2020, 04:43 AM

Dear Alansidman,

I attached a small example with the same problem. Maybe you can advise.

Thank you.

Martin

**alansidman** · 10-26-2020, 01:15 PM

Here is the Mcode I used to get the expected results. Note: PQ is case specific, ie. Null <> null.

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C
1	CSORNADR1	Customer Name	Custom
2	Customer 1	Client 1	Client 1
3	Customer 2		Customer 2
4	Customer 3	Client 2	Client 2
5	Customer 4		Customer 4
6	Customer 5		Customer 5
7	Customer 6	Client 3	Client 3
8	Customer 7		Customer 7
9	Customer 8		Customer 8
10	Customer 9		Customer 9
11	Customer 10		Customer 10
12	Customer 11	Client 4	Client 4
13	Customer 12	Client 5	Client 5
14	Customer 13		Customer 13
15	Customer 14		Customer 14
16	Customer 15		Customer 15
17	Customer 16		Customer 16
18	Customer 17		Customer 17
19	Customer 18		Customer 18
20	Customer 19	Client 6	Client 6
21	Customer 20		Customer 20
22	Customer 21	Client 7	Client 7

Sheet: Query

**MartinvanK** · 10-28-2020, 11:55 AM

Hi alansidman,

Thank you. I see that you changed the code. Below is the query I am working on. The line in red is the actual line that needs to be changed. The example was a simple version of it.
What do I need to change in this line to get it correct?

let
Source = Excel.Workbook(File.Contents("C:\Users\vankoningsbruggenm\Desktop\Dashboard\DOSP06 Daily.xlsm"), null, true),
#"DOSP06 Data_Sheet" = Source{[Item="DOSP06 Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"DOSP06 Data_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cluster", type text}, {"Country Name", type text}, {"Department", type text}, {"Product", type text}, {"Sub Product", type text}, {"Activity", type text}, {"Creation Date", Int64.Type}, {"Creation Period", type number}, {"Creation Week", type number}, {"Aging Bucket", type text}, {"Country Code", type text}, {"Company No", Int64.Type}, {"Dept No", Int64.Type}, {"Activity No", Int64.Type}, {"Serial", Int64.Type}, {"Dossier", type number}, {"Booking No", type text}, {"Booking Code", type text}, {"Entity Currency", type text}, {"FRREV- LC", type number}, {"ANCILREV- LC", type number}, {"OTHERREV- LC", type number}, {"DUTYREV- LC", type number}, {"FRCOST- LC", type number}, {"ANCILCOST- LC", Int64.Type}, {"OTHERCOST- LC", type number}, {"DUTYCOST- LC", type number}, {"Gross Revenue - LC", type number}, {"Gross Cost - LC", type number}, {"Net Revenue - LC", type number}, {"TOTALPL - LC", type number}, {"TOTALDS - LC", type number}, {"Re-opened?", type text}, {"CSORNO", Int64.Type}, {"CSORNADR1", type text}, {"House No (HAWB/HOBL)", type text}, {"Master No (MAWB/MOBL)", type text}, {"ETA Date", Int64.Type}, {"ETD Date", Int64.Type}, {"POL Loca", type text}, {"POL Country", type text}, {"POD Loca", type text}, {"POD Country", type text}, {"Unbilled?", type text}, {"User TLC", type text}, {"Create User ID", type text}, {"Create User Name", type text}, {"Corr User ID", type text}, {"Corr User Name", type text}, {"HISTTEXT", type text}, {"FRREV- USD", type number}, {"ANCILREV- USD", type number}, {"OTHERREV- USD", type number}, {"DUTYREV- USD", type number}, {"FRCOST- USD", type number}, {"ANCILCOST- USD", Int64.Type}, {"OTHERCOST- USD", type number}, {"DUTYCOST- USD", type number}, {"Gross Cost - USD", type number}, {"Net Revenue - USD", type number}, {"TOTALPL - USD", type number}, {"TOTALDS - USD", type number}, {"Gross Revenue - USD", type number}, {"Quantity", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Cluster] = "Benelux") and ([Department] = "BE.60 - Brussel - General" or [Department] = "BE.61 - GSK" or [Department] = "BE.62 - Brussel - Warehouse" or [Department] = "NL.50 - Amsterdam - General" or [Department] = "NL.52 - Amsterdam - Distribution" or [Department] = "NL.54 - Amsterdam - Oil & Gas" or [Department] = "NL.58 - Amsterdam - Vendor Finance" or [Department] = "NL.71 - Amsterdam - Medtronic" or [Department] = "NL.72 - Maastricht")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Creation Date", "Creation Period", "Creation Week", "Company No", "Booking Code", "Entity Currency", "FRREV- LC", "ANCILREV- LC", "OTHERREV- LC", "DUTYREV- LC", "FRCOST- LC", "ANCILCOST- LC", "OTHERCOST- LC", "DUTYCOST- LC", "Gross Revenue - LC", "Gross Cost - LC", "Re-opened?", "ETA Date", "ETD Date", "POL Loca", "POL Country", "POD Loca", "POD Country", "Unbilled?", "User TLC", "Create User ID", "Create User Name", "Corr User ID", "Corr User Name", "HISTTEXT", "FRREV- USD", "ANCILREV- USD", "OTHERREV- USD", "DUTYREV- USD", "FRCOST- USD", "ANCILCOST- USD", "OTHERCOST- USD", "DUTYCOST- USD", "Gross Cost - USD", "Net Revenue - USD", "TOTALPL - USD", "TOTALDS - USD", "Gross Revenue - USD", "Quantity", "TOTALDS - LC", "Net Revenue - LC"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Merged", each Text.Combine({Text.From([Dept No], "en-US"), "-", [Sub Product]}), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "Deparment Merged"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns",{"Deparment Merged"},Departments,{"Dep"},"Departments New",JoinKind.LeftOuter),
#"Expanded Departments New" = Table.ExpandTableColumn(#"Merged Queries1", "Departments New", {"Department"}, {"Departments New.Department"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Departments New",{{"Departments New.Department", "Merged Departments"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns1",{"Aging Bucket"},Aging,{"Create Date Aging Bucket"},"Aging",JoinKind.LeftOuter),
#"Expanded Aging" = Table.ExpandTableColumn(#"Merged Queries", "Aging", {"Bucket"}, {"Aging.Bucket"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Aging",{{"Deparment Merged", "Dept_Activity Merged"}, {"Merged Departments", "Department Merged"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns2",{"CSORNADR1"},#"DOSP05 Data",{"CSORNADR1"},"DOSP05 Data",JoinKind.LeftOuter),
#"Expanded DOSP05 Data" = Table.ExpandTableColumn(#"Merged Queries2", "DOSP05 Data", {"Intermediate Parent Customer"}, {"DOSP05 Data.Intermediate Parent Customer"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded DOSP05 Data",{{"DOSP05 Data.Intermediate Parent Customer", "DOSP05 Lookup"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns3", "Custom", each if Text.Contains([DOSP05 Lookup], "Null") then [CSORNADR1] else [DOSP05 Lookup]),
#"Renamed Columns4" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Customer Conditional"}})
in
#"Renamed Columns4"

**MartinvanK** · 10-28-2020, 11:56 AM

Colour not visible but it is below line:
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns3", "Custom", each if Text.Contains([DOSP05 Lookup], "Null") then [CSORNADR1] else [DOSP05 Lookup]),

**alansidman** · 10-28-2020, 12:00 PM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Power Query Conditional Column

LinkBack

Thread Tools

Rate This Thread

Display

Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Re: Power Query Conditional Column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional remove duplicates based on another column in power query related to (power bi)

[SOLVED] Power Query - excel formula translation into Power Query

[SOLVED] How to add an Excel formula code into Power Query or create a new one in Power Query

Custom Permutations Column in Power Query from two columns in the same Query

Power Query M language- Conditional Lookup/Merge

[SOLVED] Power Pivot does not load new column added in Power query

Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)

Bookmarks

Bookmarks

Posting Permissions