Forum Discussion

jagtaps's avatar
jagtaps
Frequent Visitor
4 years ago
Solved

Getting data from one of the multiple columns

Hello,

 

I have following data in power query, i need data from either of the four columns.

 

How can i get it?? if their is data in two columns its fine to get data from any column.

 

 

Can someone help me in this.

 

Regards,

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi jagtaps ,

     

    Is it this you are looking for? 🙂

     

    Here the code:

    if [Cost Center] <> null then [Cost Center] else 
if [Profit Center] <> null then [Profit Center] else 
if [Product Account] <> null then [Product Account] else 
[Order])

     

    Note, this solution is not very scalable. In case you receive a new column in the future, the added custom column will ignore the values from that column until you add the next if clause manually.

     

    Let me know, whether this helps!

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

     

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    Hi jagtaps ,

     

    Is it this you are looking for? 🙂

     

    Here the code:

    if [Cost Center] <> null then [Cost Center] else 
if [Profit Center] <> null then [Profit Center] else 
if [Product Account] <> null then [Product Account] else 
[Order])

     

    Note, this solution is not very scalable. In case you receive a new column in the future, the added custom column will ignore the values from that column until you add the next if clause manually.

     

    Let me know, whether this helps!

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     

     

     

    Table.AddColumn(#"you table or last step", "firstnotempty", each Record.FieldValues(_){List.PositionOf(Record.FieldValues(_),List.RemoveMatchingItems(Record.FieldValues(_),{null,""}){0})}

     

     

    or, to save some typing

     

     

     Table.AddColumn(#"yourtab", "firstnotempty", each let v=Record.FieldValues(_) in v{List.PositionOf(v,List.RemoveMatchingItems(v,{null,""}){0})})