Forum Discussion

DamianL's avatar
DamianL
New Member
4 years ago
Solved

Append data from multiply columns

Hi, Is it possible to append data from 10 sets of 3 columns into 3 new columns?     I have a column for each task description, target date, and owner for action to complete from 1 to 10. I ...
  • DamianL's avatar
    4 years ago

    Hi all,

    I have used the solution from below

    https://community.powerbi.com/t5/Desktop/Append-Columns-into-Column-Sets/m-p/1588923

    with small modifications to index, 

     

    I have unpivoted 30 columns in a set of data 1,2,3 - 1.1, 2.1, 3.1 - 1.2, 2.2, 3.2 - ......

     

    index need a small modification

    instead

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrSgB0YZ6MJ4RhAfnGyvF6kQrGUF5JkDaCC5nCuHB+WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"L1-Code" = _t, #"L1-Description" = _t, #"L2-Code" = _t, #"L2-Description" = _t, #"L3-Code" = _t, #"L3-Description" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"L1-Code", type text}, {"L1-Description", type text}, {"L2-Code", type text}, {"L2-Description", type text}, {"L3-Code", type text}, {"L3-Description", type text}}),
        #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1-Code", "L1-Description", "L2-Code", "L2-Description", "L3-Code", "L3-Description"}, "Attribute", "Value"),
        #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Attribute", each Text.AfterDelimiter(_, "-"), type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Extracted Text After Delimiter", "Index", 1, 1, Int64.Type),
        #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}),
        #"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[Attribute]), "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
    in
        #"Removed Columns"

     

    I have created index and divided by 3

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 3,0), type number}}),

    to create index looks like below:

     

    and then I pivot the column again creating 1,2,3 per row.

     

    thank you