none
Find the first not null column RRS feed

  • Question

  • Hi,

    I have a table like below.

    H1 H2 H3 Hm Choose1 Choose2
    100 102 103 109 100 102
    null 102 103 109 102 103
    null null  103 109 103


    m is uncertain. H1, H2... Hm are the different hierarchy prices. The hierarchy sequence is H1>H2>H3>...>Hm. Choose1 is to find the first not null hierarchy price. Choose2 is to find the second not null hierarchy price. The example shows: if H1<>null, then Choose1 =H1; if H1=null and H2<>null, then Choose1=H2;...;if H1=H2=...=Hm=null, then Choose1=null.

    The logic is simple. But how to calculate Choose1 and Choose2 in Power Query using a loop?

    Thanks


    • Edited by bjzk Tuesday, August 9, 2016 3:13 PM
    Tuesday, August 9, 2016 3:11 PM

Answers

  • Hi bjzk. Here's one way to do this:

    • Add a custom column called HValues to your table, using the following formula: List.RemoveNulls(Record.FieldValues(_))
    • Add custom columns Choose1, Choose2, etc. by using the formula pattern: [HValues]{index}
    • For example, Choose1 would be: [HValues]{0}
    • And Choose2 would be: [HValues]{1}

    Ehren

    • Marked as answer by bjzk Tuesday, August 9, 2016 6:32 PM
    Tuesday, August 9, 2016 5:16 PM
    Owner

All replies

  • Hi bjzk. Here's one way to do this:

    • Add a custom column called HValues to your table, using the following formula: List.RemoveNulls(Record.FieldValues(_))
    • Add custom columns Choose1, Choose2, etc. by using the formula pattern: [HValues]{index}
    • For example, Choose1 would be: [HValues]{0}
    • And Choose2 would be: [HValues]{1}

    Ehren

    • Marked as answer by bjzk Tuesday, August 9, 2016 6:32 PM
    Tuesday, August 9, 2016 5:16 PM
    Owner
  • I came up with something similar to Ehren's suggestion, but I used Table.ToRows with an index column:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"H1", Int64.Type}, {"H2", Int64.Type}, {"H3", Int64.Type}, {"H4", Int64.Type}}),
        AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1),
        FirstNonNull = Table.AddColumn(AddedIndex, "FirstNonNull", each try List.RemoveNulls(Table.ToRows(AddedIndex){[Index]}){0} otherwise null),
        SecondNonNull = Table.AddColumn(FirstNonNull, "SecondNonNull", each try List.RemoveNulls(Table.ToRows(AddedIndex){[Index]}){1} otherwise null),
        RemovedIndexColumn = Table.RemoveColumns(SecondNonNull,{"Index"})
    in
        RemovedIndexColumn

    However, Ehren's solution is a cleaner because it makes only one call to List.RemoveNulls:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"H1", Int64.Type}, {"H2", Int64.Type}, {"H3", Int64.Type}, {"H4", Int64.Type}}),
        AddedCustom = Table.AddColumn(ChangedType, "Custom", each List.RemoveNulls(Record.FieldValues(_))),
        FirstNonNull = Table.AddColumn(AddedCustom, "First NonNull", each [Custom]{0}),
        SecondNonNull = Table.AddColumn(FirstNonNull, "Second NonNull", each [Custom]{1}),
        RemovedCustom = Table.RemoveColumns(SecondNonNull,{"Custom"})
    in
        RemovedCustom

    Tuesday, August 9, 2016 5:42 PM
  • Hi Ehren,

    Thanks for this. This avoid using a loop to get the result. I need to change my thinking logic. Power Query is not like normal programming language. There are a lot of techniques that we can use to avoid using a loop.

    Thanks

    Tuesday, August 9, 2016 6:32 PM