Append data from multiply columns

[DamianL]

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 wonder if there is any way I can append those data to make me possible to show all task due soon or all tasks per owner.

many rows just have one task but there are some that have up to 10 also for each task can be a different owner and target date.

Thanks

[DamianL · Accepted solution]

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

[Anonymous]

Hi @DamianL ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[DamianL]

I think I found solution.

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

In my case I have text , text, date, 2text, 2text, 2date, 3text , 3text, 3date .......

And I wish to have them in new table or querry like this

text,     text,      date

2text,  2text,    2date

3text ......

hope this helps 

[Greg_Deckler]

@DamianL Seems like a straight Append query, Table.Combine. Basically, create a query that loads all the columns. Right click the query and set it to disable load. Then create additional queries, also set to disable load where each selects 3 columns. The first would be text, text, date, the second 2text, 2text, 2date, etc. Then create a final Append query that appends all of those intermediate queries together.

[Greg_Deckler]

@DamianL It's probably possible but generally for Power BI you want just the 3 columns and Append them all together so that you end up with 3 columns in the end. It *usually* works better that way for building visuals. But, would need to see maybe some sample data and what you are trying to achieve. If you are doing a Merge, I don't see what you would be merging on?