# Find the first not null column

• ### 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 Tuesday, August 9, 2016 3:13 PM
Tuesday, August 9, 2016 3:11 PM

• 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 Tuesday, August 9, 2016 6:32 PM
Tuesday, August 9, 2016 5:16 PM

### 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 Tuesday, August 9, 2016 6:32 PM
Tuesday, August 9, 2016 5:16 PM
• 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}}),
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}}),