Blank cell needs to be filled depending on multiple conditions (or conditional column)

[OvidiuNeacsu]

Hello everyone, 

The picture below describes very well what I'm trying to do. I have an unpivoted database in Power Query with each step of the recruitment process (Folder) which recruiter (User) has done the steps and I have an automatic process in the system that leaves "Offer Accepted" user blank. I'm having a hard time filling that blank cell.

The logic I'm looking for is IF John Sent the Offer and the Offer was accepted (therefore has a date) then fill the User cell with John in front of Offer Accepted. I'm guessing a conditional column is the best option but I'm stuck... Any thoughts? 

Thank you very much for all your support and understanding, 

Ovidiu

[amitchandak]

@OvidiuNeacsu , refer if these can help

https://www.edureka.co/community/40467/replace-null-values-custom-values-power-power-query-editor

https://www.youtube.com/watch?v=-fwKhMot9hw

https://www.youtube.com/watch?v=34ZMM4vajos

[HotChilli · Accepted solution]

Add a custom column (edit the following code for your circumstances)

if [User] = null and [Date] <> null and [Folder] = "Offer Accepted" then List.First(
            Table.SelectRows(#"Renamed Columns2",
                (r) =>
                    r[ID] = [ID]
                    and
                    r[Folder] = "Offer Sent"
            )[User] , null 
        ) else [User]

Tip : Please post data (not a picture ) for better answers

[OvidiuNeacsu]

Hi @HotChilli 

It seems to work, but I receive an error in front of Offer Accepted. Not sure if i did something wrong. 

Expression.Error: A cyclic reference was encountered during evaluation.

The adapted code is: 

if [Value.1] = "" and [Value.2] <> null and [Attribute] = "Candidate Folder history_Offer - Accepted" then List.First(
            Table.SelectRows(#"Folder Movement",
                (r) =>
                    r[Candidate_CandidateGUId] = [Candidate_CandidateGUId]
                    and
                    r[Attribute] = "Candidate Folder history_Offer"
            )[Value.1] , null 
        ) else [Value.1]

[HotChilli]

Are you able to debug from your side?

If not, post a small sample of data and I may get to it later.

[OvidiuNeacsu]

I made the test using the excel from the original post. Same outcome. 

It's interesting that even though it's showing the Error in Power Query, in visualization it gives me the right format. 

https://we.tl/t-SOklZ0dP6y 

As I said, it seems to work very well. Not sure why the error appears. 

Thank you very much for this @HotChilli . I would pretty much appreciate it if you can figure out why the error appears. In a large database, it seems to behave rather strange. 

Have a great day, 

Ovidiu

[HotChilli]

Hello @OvidiuNeacsu 

Thanks for the pbix and data. 
The problem is from the Table.SelectRows part 

It should be: 

Table.SelectRows(#"Changed Type",

 The original was referring to Sheet1 or Source (can't remember) but it just needed to refer to the Previous Step in the Applied Steps.

Interesting that it threw the error but got the correct answer when saved.

Hope it works on the larger dataset, the small dataset wouldn't cover all test cases.

Good luck.

[OvidiuNeacsu]

hi @HotChilli 

Would you know why the 65 MB database turned into an 800 MB after I applied the custom column?

It was taking a while to refresh because of the unpivot, not it takes 10 times more. 

Any ways to optimize it? 

Thank you very much for that code, it works great!

Have a great day, 

Ovidiu

[HotChilli]

Are you talking about the memory footprint?  I would expect the "Mashup Evaluation Container" to spike during refresh and then drop once everything is in the powerbi model (unless there are lots of complex calculated columns in DAX). (Check Task Manager) 

You might experiment with the 'Allow data preview to download in the background' option.

Switch off 'Auto Date/time'

You could also try wrapping Table.Buffer round the appropriate part of the query.

Have a look at this : https://www.thebiccountant.com/speedperformance-aspects/ 

[OvidiuNeacsu]

Pure Genius !!! Thank you very much! @HotChilli