transform multiple columns into 2 columns (List.Zip, (un)pivot)

[WMart_AMS]

Hi Everybody,

Hopefully somebody can help me out. I am working with the data set in the image below. basically i have 5 categrories: Scope, Planning, Financien, Capaciteit and Kwaliteit. These categories are provided with an indicator and a comment. Each row contains the indicators and comments of 1 project in one specific period of time, this is represented with the index column.

My desired result is as following. I tried to work with List.Zip and unpivotting, but it does not give me the correct results. Can somebody help me out? The data table is provided below.

Index	Project_code	Scope opmerkingen	Scope indicator	Kwaliteit opmerkingen	Kwaliteit indicator	Planning opmerkingen	Planning indicator	Capaciteit opmerkingen	Capaciteit indicator	Financien opmerkingen	Financien indicator
1	7304100171	Tekst scope 1	3	Tekst kwaliteit1	2	Tekst planning1	2	Tekst capaciteit1	2	Tekst financien1	3
1	7304210056	Tekst scope 2	3	Tekst kwaliteit2	3	Tekst planning2	2	Tekst capaciteit2	2	Tekst financien2	2
1	7304210032	Tekst scope 3	3	Tekst kwaliteit3	3	Tekst planning3	2	Tekst capaciteit3	2	Tekst financien3	2
1	7304210053	Tekst scope 4	null	Tekst kwaliteit4	null	Tekst planning4	null	Tekst capaciteit4	null	Tekst financien4	null
1	7304210033	Tekst scope 5	null	Tekst kwaliteit5	null	Tekst planning5	null	Tekst capaciteit5	null	Tekst financien5	null
2	7304100171	Tekst scope 6	3	Tekst kwaliteit6	2	Tekst planning6	2	Tekst capaciteit6	2	Tekst financien6	3
2	7304210056	Tekst scope 7	3	Tekst kwaliteit7	3	Tekst planning7	2	Tekst capaciteit7	2	Tekst financien7	2
2	7304210032	Tekst scope 8	3	Tekst kwaliteit8	3	Tekst planning8	2	Tekst capaciteit8	2	Tekst financien8	2
2	7304210053	Tekst scope 9	null	Tekst kwaliteit9	null	Tekst planning9	null	Tekst capaciteit9	null	Tekst financien9	null
2	7304210033	Tekst scope 10	null	Tekst kwaliteit10	null	Tekst planning10	null	Tekst capaciteit10	null	Tekst financien10	null
3	7304100171	Tekst scope 11	3	Tekst kwaliteit11	2	Tekst planning11	2	Tekst capaciteit11	2	Tekst financien11	3
3	7304210056	Tekst scope 12	3	Tekst kwaliteit12	3	Tekst planning12	2	Tekst capaciteit12	2	Tekst financien12	2
3	7304210032	Tekst scope 13	3	Tekst kwaliteit13	3	Tekst planning13	2	Tekst capaciteit13	2	Tekst financien13	2

[sevenhills · Accepted solution]

One more simple way:

Your Source:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZSxTsMwEEB/5ZS5Q+JQuheEijpQkbHq4CaH6yqxIzcpEiPfg8RH8Cd8CVcLQomjKm3MYF3s2C+Xp/Mtl0EUjIJJHF5FYRhNIqBZkuoSKcY0ZljBXmuzRoMyk0rs1nUmaFHuQJcCN1qLDApa+Hx9i4PVqA2kyfyZ57JCWdEzo3GLsEEsDOfZASMVTStYJA+w5wrYGJ5wbWpuJAj8eFfd0EXOlaJ8vpn3CjKEpKprI4zG0qIyTLGgzOFFbu370ugtphV2I294ydOfRFWd5xSmcns4uJg+zoFztaekhRVyWBW1TcF+i36hm3onFVFR/TJtON7LaPP4epD6Dp5n8w3Un/kG6dV8Q+1pPmZexVucb/EW6le8RXoXb6knxLP29TgWf4klB9hWf4Emh9kyf7YhB9jl/fyyc7A9xDe3Y1jFOzgfFe9Ah1e8g/RS8Q61p/iYDW/yf3n/YN5Tr3GQ3s2f7DWrLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Project_code = _t, Category = _t, Indicator = _t, Comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Project_code", Int64.Type}, {"Category", type text}, {"Indicator", Int64.Type}, {"Comments", type text}})
in
    #"Changed Type"

Target:

let
    Source = Table,
    #"Replaced Value" = Table.ReplaceValue(Source,null,-99999,Replacer.ReplaceValue,{"Indicator"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","null","-9999990",Replacer.ReplaceValue,{"Comments"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"Index", "Project_code", "Category"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Category", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Scope Indicator", type text}, {"Scope Comments", type text}, {"Kwaliteit Indicator", type text}, {"Kwaliteit Comments", type text}, {"Planning Indicator", type text}, {"Planning Comments", type text}, {"Capaciteit Indicator", type text}, {"Capaciteit Comments", type text}, {"Finacien Indicator", type text}, {"Finacien Comments", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type","-99999",null,Replacer.ReplaceValue,{"Scope Indicator", "Kwaliteit Indicator", "Planning Indicator", "Capaciteit Indicator", "Finacien Indicator"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","-9999990",null,Replacer.ReplaceValue,{"Scope Comments", "Kwaliteit Comments", "Planning Comments", "Capaciteit Comments", "Finacien Comments"})
in
    #"Replaced Value3"

Output:

I used replace with some standard value for nulls and after the format achieved, replaced back to nulls. 

Hope this helps!

[dufoq3]

Hi @WMart_AMS, query for your sample data:

Output

let
    Source = Table.TransformColumns(Table.FromColumns(List.Split(Text.Split("1,1,1,1,1,2,2,2,2,2,3,3,3,7304100171,7304210056,7304210032,7304210053,7304210033,7304100171,7304210056,7304210032,7304210053,7304210033,7304100171,7304210056,7304210032,Tekst scope 1,Tekst scope 2,Tekst scope 3,Tekst scope 4,Tekst scope 5,Tekst scope 6,Tekst scope 7,Tekst scope 8,Tekst scope 9,Tekst scope 10,Tekst scope 11,Tekst scope 12,Tekst scope 13,3,3,3,,,3,3,3,,,3,3,3,Tekst kwaliteit1,Tekst kwaliteit2,Tekst kwaliteit3,Tekst kwaliteit4,Tekst kwaliteit5,Tekst kwaliteit6,Tekst kwaliteit7,Tekst kwaliteit8,Tekst kwaliteit9,Tekst kwaliteit10,Tekst kwaliteit11,Tekst kwaliteit12,Tekst kwaliteit13,2,3,3,,,2,3,3,,,2,3,3,Tekst planning1,Tekst planning2,Tekst planning3,Tekst planning4,Tekst planning5,Tekst planning6,Tekst planning7,Tekst planning8,Tekst planning9,Tekst planning10,Tekst planning11,Tekst planning12,Tekst planning13,2,2,2,,,2,2,2,,,2,2,2,Tekst capaciteit1,Tekst capaciteit2,Tekst capaciteit3,Tekst capaciteit4,Tekst capaciteit5,Tekst capaciteit6,Tekst capaciteit7,Tekst capaciteit8,Tekst capaciteit9,Tekst capaciteit10,Tekst capaciteit11,Tekst capaciteit12,Tekst capaciteit13,2,2,2,,,2,2,2,,,2,2,2,Tekst financien1,Tekst financien2,Tekst financien3,Tekst financien4,Tekst financien5,Tekst financien6,Tekst financien7,Tekst financien8,Tekst financien9,Tekst financien10,Tekst financien11,Tekst financien12,Tekst financien13,3,2,2,,,3,2,2,,,3,2,2", ","), 13), {"Index", "Project_code", "Scope opmerkingen", "Scope indicator", "Kwaliteit opmerkingen", "Kwaliteit indicator", "Planning opmerkingen", "Planning indicator", "Capaciteit opmerkingen", "Capaciteit indicator", "Financien opmerkingen", "Financien indicator"}), {}, each if _ = "" then null else _),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each List.Split(List.Skip(_, 2), 2),
        (x,y)=> {x{0}, x{1}} & y ), {"Index", "Project_code", "Category", "Indicator"})
in
    Transformed

[AntrikshSharma]

@WMart_AMS 

let
    Source = Table,
    Transform = Table.TransformRows (
        Source,
        (Row) =>
            let
                ColumnValues = List.Skip ( Record.ToList ( Row ), 2 ),
                ColumnNames = List.Skip ( Record.FieldNames ( Row ), 2 ),
                Comments = List.Alternate ( ColumnValues, 1, 1, 1 ),
                Indicator = List.Alternate ( ColumnValues, 1, 1 ),
                Category = List.Distinct (
                    List.Transform ( ColumnNames, ( x ) => Text.BeforeDelimiter ( x, " " ) )
                ),
                ColumnsCombined = Table.FromColumns (
                    { { Row[Index] } }
                        & { { Row[Project_code] } }
                        & { Category }
                        & { Indicator }
                        & { Comments },
                    type table [
                        Index = Int64.Type,
                        Project Code = Int64.Type,
                        Category = text,
                        Indictory = Int64.Type,
                        Commments = text
                    ]
                ),
                FillDownColums = Table.FillDown ( ColumnsCombined, { "Index", "Project Code" } )
            in
                FillDownColums
    ),
    CombineTables = Table.Combine ( Transform )
in
    CombineTables

[sevenhills · Accepted solution]

One more simple way:

Your Source:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZSxTsMwEEB/5ZS5Q+JQuheEijpQkbHq4CaH6yqxIzcpEiPfg8RH8Cd8CVcLQomjKm3MYF3s2C+Xp/Mtl0EUjIJJHF5FYRhNIqBZkuoSKcY0ZljBXmuzRoMyk0rs1nUmaFHuQJcCN1qLDApa+Hx9i4PVqA2kyfyZ57JCWdEzo3GLsEEsDOfZASMVTStYJA+w5wrYGJ5wbWpuJAj8eFfd0EXOlaJ8vpn3CjKEpKprI4zG0qIyTLGgzOFFbu370ugtphV2I294ydOfRFWd5xSmcns4uJg+zoFztaekhRVyWBW1TcF+i36hm3onFVFR/TJtON7LaPP4epD6Dp5n8w3Un/kG6dV8Q+1pPmZexVucb/EW6le8RXoXb6knxLP29TgWf4klB9hWf4Emh9kyf7YhB9jl/fyyc7A9xDe3Y1jFOzgfFe9Ah1e8g/RS8Q61p/iYDW/yf3n/YN5Tr3GQ3s2f7DWrLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Project_code = _t, Category = _t, Indicator = _t, Comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Project_code", Int64.Type}, {"Category", type text}, {"Indicator", Int64.Type}, {"Comments", type text}})
in
    #"Changed Type"

Target:

let
    Source = Table,
    #"Replaced Value" = Table.ReplaceValue(Source,null,-99999,Replacer.ReplaceValue,{"Indicator"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","null","-9999990",Replacer.ReplaceValue,{"Comments"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"Index", "Project_code", "Category"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Category", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Scope Indicator", type text}, {"Scope Comments", type text}, {"Kwaliteit Indicator", type text}, {"Kwaliteit Comments", type text}, {"Planning Indicator", type text}, {"Planning Comments", type text}, {"Capaciteit Indicator", type text}, {"Capaciteit Comments", type text}, {"Finacien Indicator", type text}, {"Finacien Comments", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type","-99999",null,Replacer.ReplaceValue,{"Scope Indicator", "Kwaliteit Indicator", "Planning Indicator", "Capaciteit Indicator", "Finacien Indicator"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","-9999990",null,Replacer.ReplaceValue,{"Scope Comments", "Kwaliteit Comments", "Planning Comments", "Capaciteit Comments", "Finacien Comments"})
in
    #"Replaced Value3"

Output:

I used replace with some standard value for nulls and after the format achieved, replaced back to nulls. 

Hope this helps!

[WMart_AMS · Accepted solution]

I tried this out and it worked! Thanks!

[SundarRaj]

Hi @WMart_AMS , here's another solution you could possibly look at. I'll leave the images of the output, the source and text of code used. Thanks!

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Project_code", Int64.Type}, {"Scope opmerkingen", type text}, {"Scope indicator", Int64.Type}, {"Kwaliteit opmerkingen", type text}, {"Kwaliteit indicator", Int64.Type}, {"Planning opmerkingen", type text}, {"Planning indicator", Int64.Type}, {"Capaciteit opmerkingen", type text}, {"Capaciteit indicator", Int64.Type}, {"Financien opmerkingen", type text}, {"Financien indicator", Int64.Type}}),

Custom1 = List.Split(List.Skip(Table.ToColumns(#"Changed Type"),2),2),

Custom2 = List.Transform(Custom1, each Table.FromColumns(_)),

Custom3 = List.Transform(Custom2, each Table.AddIndexColumn(_,"Project_Code",0,1)),

Custom4 = List.Transform(Custom3, each Table.TransformColumns(_, {"Project_Code", each #"Changed Type"[Project_code]{_}})),

Custom5 = List.Transform(Custom4, each Table.AddIndexColumn(_,"Index",0,1)),

Custom6 = List.Transform(Custom5, each Table.TransformColumns(_, {"Index", each #"Changed Type"[Index]{_}})),

Custom7 = Table.Combine(Custom6),

#"Reordered Columns" = Table.ReorderColumns(Custom7,{"Index", "Project_Code", "Column1", "Column2"}),

#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Index","Project_Code"}, {{"All", each _, type table [Index=number, Project_Code=number, Column1=nullable text, Column2=nullable number]}})[All],

Custom8 = List.Transform(#"Grouped Rows", each Table.AddIndexColumn(_, "Category",0,1)),

Custom9 = {"Scope","Kwaliteit","Planning","Capaciteit","Financien"},

Custom10 = List.Transform(Custom8, each Table.TransformColumns(_,{"Category", each Custom9{_}})),

Custom11 = Table.Combine(Custom10),

#"Reordered Columns1" = Table.ReorderColumns(Custom11,{"Index", "Project_Code", "Category", "Column2", "Column1"}),

#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Column2", "Indicators"}, {"Column1", "Comments"}})

in

#"Renamed Columns"

[slorin]

Hi @WMart_AMS 

A solution with List.TransformMany

let
Source = Your_Source,
ColumnNames = Table.ColumnNames(Source),
Data = Table.ToRows(Source),
Transform = List.TransformMany(Data, each {1..5},
  (x,y) => List.FirstN(x,2) & {Text.BeforeDelimiter(ColumnNames{y*2+1}, " ")} & List.Reverse(List.Range(x,y*2,2))),
Result = Table.FromRows(Transform, {"Index", "Project_code", "Category", "Indicator", "Comment"})
in
Result

or

let
Source = Your_Source,
ColumnNames = List.Distinct(List.Transform(
  List.RemoveFirstN(Table.ColumnNames(Source),2),
  each Text.BeforeDelimiter(_, " "))),
Data = Table.ToRows(Source),
Transform = List.TransformMany(Data, each {1..5},
  (x,y) => List.FirstN(x,2) & {ColumnNames{y-1}} & {x{y*2+1}} & {x{y*2}}),
Result = Table.FromRows(Transform, {"Index", "Project_code", "Category", "Indicator", "Comment"})
in
Result

Stéphane

[jgeddes]

Maybe something like this may work...

let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdRNioNAEAXgq4ReZ2F1Rdu5x+yCCxEnNJGOEIe5fiKZrv6pKpe+F8KHFu96NWDOxmFzgaYBtz98z/fndnpOj3U+7c9I2f1vXPw2+22PLcXrMobgw61Mp3EdJ+HXPz6MYfJz+Pz3cE4E+za0XUWwMqGMI8EqBCsTPnFNQFsRUCagSECFgDIBRUKLFeHyfg6/y8IVrIkQViQLq4hDDXsptahVRayJIlYkEatI1OYie3SsnfyZOvFYyzRBypwUHR2rNQfH6mRCGUeCUwhOJji6lJzAjrWXCb1I6BVCLxN6kcCO9Us9DdZECCuShVXEoYa9lFoEjUriFe0Za7JNY13atSZn4eG8avuqDKy6sNrEpo1Fc3C2oIwsyCsL2syCsrOQhjZ3sNsFZWlBnlrQthaUsf3Ph+EF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Project_code = _t, #"Scope opmerkingen" = _t, #"Scope indicator" = _t, #"Kwaliteit opmerkingen" = _t, #"Kwaliteit indicator" = _t, #"Planning opmerkingen" = _t, #"Planning indicator" = _t, #"Capaciteit opmerkingen" = _t, #"Capaciteit indicator" = _t, #"Financien opmerkingen" = _t, #"Financien indicator" = _t]),
    set_types = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Index", Int64.Type}, {"Project_code", Int64.Type}, {"Scope opmerkingen", type text}, {"Scope indicator", Int64.Type}, {"Kwaliteit opmerkingen", type text}, {"Kwaliteit indicator", Int64.Type}, {"Planning opmerkingen", type text}, {"Planning indicator", Int64.Type}, {"Capaciteit opmerkingen", type text}, {"Capaciteit indicator", Int64.Type}, {"Financien opmerkingen", type text}, {"Financien indicator", Int64.Type}
        }
    ),
    indicator_columns = 
    List.Select(
        Table.ColumnNames(set_types), 
        each Text.Contains(_, "indicator")
    ),
    opmerkingen_columns = 
    List.Select(
        Table.ColumnNames(set_types), 
        each Text.Contains(_, "opmerkingen")
    ),
    columns_list = 
    List.Transform(
        opmerkingen_columns, 
        each Text.BeforeDelimiter(_, " ")
    ),
    group_rows = 
    Table.Group(
        set_types, 
        {"Index", "Project_code"}, 
        {
            {"AllRows", each List.Zip({columns_list, Record.ToList(Table.SelectColumns(_, indicator_columns){0}), Record.ToList(Table.SelectColumns(_, opmerkingen_columns){0})}), type list}
        }
    ),
    expand_nested = 
    Table.ExpandListColumn(
        group_rows, 
        "AllRows"
    ),
    extract_text = 
    Table.TransformColumns(
        expand_nested, 
        {
            {"AllRows", each Text.Combine(List.Transform(List.ReplaceValue(_, null, "", Replacer.ReplaceValue), each Text.From(_)), "|")}
        }
    ),
    split_column = 
    Table.SplitColumn(
        extract_text, 
        "AllRows", 
        Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
        {"Category", "Indicator", "Comment"}
    )
in
    split_column

[ToddChitt]

You might be able to do this with five separate queries, where each one does an UNPIVOT of one pair of columns. Then have a final query that does a big UNION of the five.