Power Query Nested If Function/Conditional Column Help

Question

Hello,I am doing some work in Power Query and would like to create either a column that will tell me the last contact number and it's associated status. The action item can be resolved prior to the final (6th) status, and I am having issues creating a conditional column with PQ that will 'read back' from 6th contact status to find the contact that is not in a null status (aka not attempted), and then pull that filled status and its respective contact number.I am unfamiliar with coding in M/Dax so any assistance would be appreciated! I have attached an example sample file. Thank you!

https://www.dropbox.com/scl/fi/4f0uyzsvpwsuz5oqrnb7d/PowerQuery-Contact-Sample-File.xlsx?rlkey=5us1h2cwomhldd6obz584gv7d&st=zmn2xgta&dl=0

Answer 1

I would do it this way.

Andreas.

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1st Contact Date", type date}, {"2nd Contact Date", type date}, {"3rd Contact Date", type date}, {"4th Contact Date", type date}, {"5th Contact Date", type date}, {"6th Contact Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Last", each Record.FromList(List.LastN(List.RemoveNulls(Record.ToList(_)),2), {"Last Date", "Last Status"})),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Added Custom", "Last", {"Last Date", "Last Status"}, {"Last Date", "Last Status"})
in
    #"Expanded {0}"

Answer 2

Hi Austin

I'm writing a follow up this thread, as we haven’t received any news from you in a few days, may we know have you checked the replies given by our colleagues?

Feel free to post back if you need further assistance.

Regards

Jeovany

Answer 3

Hi,

This M code works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"1st Contact Date", type datetime}, {"1st Contact Status", type text}, {"2nd Contact Date", type datetime}, {"2nd Contact Status", type text}, {"3rd Contact Date", type datetime}, {"3rd Contact Status", type text}, {"4th Contact Date", type datetime}, {"4th Contact Status", type text}, {"5th Contact Date", type datetime}, {"5th Contact Status", type text}, {"6th Contact Date", type datetime}, {"6th Contact Status", type text}}), 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Last status", each List.Last(List.RemoveNulls(Table.SelectRows(Record.ToTable(\_), each Text.Contains(\_[Name],"Status",Comparer.OrdinalIgnoreCase))[Value]))), 

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Last date", each List.Last(List.RemoveNulls(Table.SelectRows(Record.ToTable(\_), each Text.Contains(\_[Name],"Date",Comparer.OrdinalIgnoreCase))[Value])), type date) 

in

#"Added Custom1"

Hope this helps.

Answer 4

Hello,

Thank you for reaching out, Andreas solution worked for my post item!

Thank you again for the assistance!