How to split and then append table?

[Ngoc_Nguyen]

Hi, 

My table has multiple groups of "User" columns and same set of question columns that are currently spreaded horizontally. I would like to somehow split those groups and then append to the table so I can have a table with a single column "User" and a single group of question columns. Please note that the number of questions for each set is varied, so some groups have full 20 questions, some have none but the "user" column.

Anything help is welcomed and appreciated. Thank you!

Take a look at the image below for better understanding of what I have and want to achieve.

Currenly have:

Location	Date	C3.01.01User	C3.01.02Question1	C3.01.03Question2	C3.01.4Question3	C3.02.01User	C3.03.01User	C3.03.02Question1	C3.03.03Question2
A		001	xx	xx	xx	null	002	xx	xx
A		002	xx	xx	xx	null	003	xx	xx
A		001	xx	xx	xx	null	null	xx	xx

Want to have:

Location	Date	User	Question1	Question2	Question3
A		001
A		002
A		003

[v-stephen-msft · Accepted solution]

Hi @Ngoc_Nguyen ,

Here's my outcome based on my solution.

The whole codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9T0lFySSxJBVLOxnoGhkAUWpxahOAaBZamFoMUGiLEjGFiRnAxE5iQMVTICNUkYwwuhsHGKAbH6kQrOQJljAyMjHUNdUGKDAxAZEUFCpFXmpMDljNCEsaq2QiPZmNCmnHbDKXgumMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Date", "Location"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 6}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Attribute.2],"User") then [Value] else null),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each if [Custom] <> null then [Value]&"-"&Number.ToText([Index])  else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom.1"}, {{"All Rows", each _, type table [Location=nullable text, Date=nullable text, Attribute.1=nullable text, Attribute.2=nullable text, Value=text, Custom=nullable text, Index=number, Custom.1=text]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Index1", "All Rows", {"Location", "Date", "Attribute.2", "Value"}, {"Location", "Date", "Attribute.2", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All Rows",{"Custom.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Location", "Date"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Attribute.2]), "Attribute.2", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([01User] <> "null"))
in
    #"Filtered Rows"

You can also download my attachment for more details.

Best Regards,

Stephen Tao

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