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

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

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.

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

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"

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

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