none
Search row for value then insert text in additional column based on header of column where value was found RRS feed

  • Question

  • Hello! I'm new to Power Query, but not new to programming, excel formulas, or SQL queries, and I'm a little stuck. I have 6 columns with headers that denote a Location and Time (ex: col1=Ashburn [9am] ; col2=Haleyville [noon] ; etc.). In each column, cell contents are either 'null' or '1' (only one of the 6 columns will have a '1'). I need to be able to look across the 6 columns, find the column that is not null and then read the header and put 'Ashburn' in an added Location column and '9am' in an added Time column. If anyone can help me out with this I'd be super grateful!

    Thanks in advance for any assistance!


    • Edited by pkirill-vea Thursday, October 24, 2019 3:30 PM clarity
    Thursday, October 24, 2019 3:29 PM

Answers

  • This code should work:

    let
        i = Table.AddIndexColumn(YourTable, "i", 0, 1),
        unpivot = Table.UnpivotOtherColumns(i, {"i"}, "Column", "Value"),
        join = Table.NestedJoin(i,"i",unpivot,"i","temp"),
        expand = Table.ExpandTableColumn(join, "temp", {"Column"}),
        split = Table.SplitColumn(expand, "Column", Splitter.SplitTextByDelimiter(" ("), {"Location", "Time"}),
        trim = Table.TransformColumns(split,{{"Location", each Text.Trim(Text.AfterDelimiter(_, "Event Locations_"))}, {"Time", each Text.Trim(_, ")")}})
    in
        trim
    Thursday, October 24, 2019 4:23 PM

All replies