Transpose Power query based condition

[Sarath5c8]

I have a single column data which need to be transformed in list once expanded the required output should be in given format.

Input.

Value

John

K

USA

NJ

Add Ken

Ken

S

UK

London

67078

Add Sara

Sara

Jenny

JPN

Tokyo

4567-354

Expected Output: 

FirstName	LastName	Country	State	PostalCode
John	K	USA	NJ	null
Ken	S	UK	London	67078
Sara	Jenny	JPN	Tokyo	4567-354

the split should happen based the condition where "Add" word is present and the next one is the new record. If corresponding attribute is not present then null should be displayed example: the first record Postal code is not present hence null.

[Anonymous · Accepted solution]

Hi @Sarath5c8 

You can put the following code to Advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFOK1YlW8gaTocGOYNrPC0w5pqQoeKdCFUDpYIhCiHqf/LyUfIi4mbmBuQVcV3BiUSJEOYzhlZqXVwlhBfiB6ZD87Mp8MMvE1Mxc19jURCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=1 or Text.Contains([Value],"Add") then Text.Trim(Text.Replace([Value],"Add","")) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Value], "Add")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Index"}, {"Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US")[Index]), "Index", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"1", "FirstName"}, {"2", "LastName"}, {"3", "Country"}, {"4", "State"}, {"5", "PostalCode"}})
in
    #"Renamed Columns"

Output

Best Regards!

Yolo Zhu

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

[AlienSx]

(upd)

let
    Source = single_column_data_table,
    type_text = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    cols = {"FirstName", "LastName", "Country", "State", "PostalCode"},
    make_record = (tbl as table) =>
        [skip = Byte.From(Text.StartsWith(Table.FirstValue(tbl), "Add ")),
        rec = Record.FromList(
            List.Skip(tbl[Value], skip), 
            List.FirstN(cols, Table.RowCount(tbl) - skip))][rec],
    gr = Table.Group(
        type_text, "Value", {{"r", make_record}},
        GroupKind.Local, (s, c) => Byte.From(Text.StartsWith(c, "Add "))),
    z = Table.FromRecords(gr[r], cols, MissingField.UseNull)
in
    z