Unpivot multiple columns but keep certain columns separate?

[Cbutler]

Hi, Fairly new to Power BI and I am trying to make a bilingual report without having a load of lookups. 

I have a data table with a load of columns, each measure has a Welsh name column and English name column. How can I unpivot these columns so that they show the Welsh and English on the rows but then keep separate the different measures? 

eg, I have mocked up an example below, 

I need to merge these 6 columns in to 3 columns, (School name, Catchment area, school type) and only want 10 rows in total, one for each "school" Welsh 1 to 5 and English 1 to 5, and have another column that I can use to filter between Welsh and English. 

This is how I want the data to look....

Am I making this much harder than it needs to be? 

[v-eachen-msft]

Hi @Cbutler ,

After Unpivot columns, you could use Table.SplitColumn(), SelectRows(), SelectColumns() to get your result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcqxCcAwDETRVYxqN5FO0xhVJhsEvH6w+M0dH95adsZj097eMza16Y++X/Nq73a0ox3taEdHd6ADHehAB1rdQgsttNBCZ3eiE53oRKdV/Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"W school name" = _t, #"E school name" = _t, #"W catchment" = _t, #"E catchment" = _t, #"W type" = _t, #"E type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"W school name", type text}, {"E school name", type text}, {"W catchment", type text}, {"E catchment", type text}, {"W type", type text}, {"E type", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"W school name", "E school name", "W catchment", "E catchment"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"W school name", "E school name", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Unpivoted Columns1", {"Attribute", "Value", "Attribute.1", "Value.1"}, "Attribute.2", "Value.2"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns2", "Custom", each [Value.2]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each [Value]),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Added Custom1", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Custom.1.1", "Custom.2.1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1.1", type text}, {"Custom.2.1", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each [Value.1]),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Added Custom2", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Custom.1.2", "Custom.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Custom.1.2", type text}, {"Custom.2.2", type text}}),
    RemoveRows = Table.SelectRows(#"Changed Type3",each [Custom.1]=[Custom.1.1] and [Custom.1]=[Custom.1.2]),
    SelectColumns = Table.SelectColumns(RemoveRows,{"Custom.1","Value.2","Value.1","Value"})
in
    SelectColumns

Here is my test file. You could open advanced editor and refer to my M query.

[Cbutler]

Hi @v-eachen-msft 

Wow thanks, this might be a bit complex when I come to apply to my report, we have 80 schools in total and about 15 measures (so far)...

Do you know if there is a way to write a measure that says if "English" is selected display column 1,3,5,7 or if "Welsh" is selected display columns 2,4,6,8 etc? Or maybe another measure similar to look at a different table eg if "English" is selected look at table "A" else use table "B"?

This way I could use a simple language look up that would witch between the two? Is something like this possible?