Conditional Unpivot in powerquery

Question

In power query I would like to unpivot my table based on a condition: unpivot all columns except those that start with "IP_address" or "Please state your".

Is this possible?

Answer 1

Here's one way:

If you start with a table like this:

You can click the to the left of the formula bar and replace what appears in the formula bar with this:

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, List.RemoveNulls(List.Generate(
()=>[A=Table.ColumnNames(Source), Index=0],
each [Index] < List.Count([A]),
each [A=[A],Index =[Index]+1],
each if Text.Start([A]{[Index]},10) = "IP_Address" then [A]{[Index]} 
    else if Text.Start([A]{[Index]},18) = "Please state your" then [A]{[Index]} 
    else null 
)), "Attribute", "Value")

To get this:

Here my complete query code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, List.RemoveNulls(List.Generate(
()=>[A=Table.ColumnNames(Source), Index=0],
each [Index] < List.Count([A]),
each [A=[A],Index =[Index]+1],
each if Text.Start([A]{[Index]},10) = "IP_Address" then [A]{[Index]} 
    else if Text.Start([A]{[Index]},18) = "Please state your" then [A]{[Index]} 
    else null 
)), "Attribute", "Value")
in
#"Unpivoted Other Columns"

I just noticed I used "IP_Address" (with a capital A) instead of "IP_address" (with a lower case A), so you'll need to adjust for that.