Search code examples
powerquery

Power Query Extract Value from Rows

I have this powerquery that I want to extract the values highlighted in Row 5 to another column. I can easily use

Record.Field(#"Added Custom"{4},"Column17")

But the problem I have is that this value can appear anywhere from column 12 to column 20 but still on Row 5. How can I find it within that row to extract it. Thank you. enter image description here

Solution

  • To find the only non-null value in row 5 between columns 12 and 20, use this, replacing the word Source with your #"Prior Step Name" likely #"Added Custom"

    Value = List.RemoveNulls(Record.ToList(Table.SelectColumns(Source,List.Range(Table.ColumnNames(Source),11,9)){4})){0}

    Alternate version that looks for first text containing { in row 5 between columns 12 and 20

    Value = List.RemoveNulls(List.Transform(List.RemoveNulls(Record.ToList(Table.SelectColumns(Source,List.Range(Table.ColumnNames(Source),11,9)){4})), each if Text.Contains(_,"{") then _ else null)){0}

    version 2 Try this.... add column ... custom column that has fomula

    = try List.Transform(List.RemoveNulls(List.Range(Record.FieldValues(_),11,9)), each if Text.Contains(_,"{") then _ else null){0} otherwise null

    then right click column and fill down, then right click and fill up