So I

[Jenni98812]

So this is a bit hard to explain...
I have a sheet containing data of all 50 states in columns a sort of yes/no of availability in each state. There are instances when one row contains "READY TO SELL" in multiple cells in the row.
I need to be able to return the Column header Value (ie, AL, AR, AZ... Etc) to a new column AND copy the row if there is a second, third, or more state that is ready to sell.
So in theory, say the record in Row 4 in the example pic I included, it would have one record with AL, and a second record with FL, and also copy the row data to both records.

Thanks!!

 

[arunsjain]

Hi,

I hope it will help.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Upper, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each [Value] = "READY TO SELL"),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Attribute", type text}, {"Value", type text}})
in
    #"Changed Type"