Trying to transpose and rearranging my data and couldn't figure out .

[Anonymous]

I have millions of rows of data as given in the example attached file (input/output) tabs and I can not figure out the easy way. Could you please help.

Output table is required and input which is below in the post is table format of current data. 

output 

Customer	cust_brand	Brand	OS	Market	payment_amt	activation_date	Since_Joined	Products	Amount
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Cookies	13
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Bread	1.5
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Shampoo	0.8
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Blanket	0.6
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Door	0.5
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Chair	0.3
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Glass	0.2
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Table	0.2
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Cups	0.1
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Books	0.12
Julee	Data Advanture	Cubes	Android	New York	20	########	2558.32	Others	2.68

Input

Customer

cust_brand

Brand

OS

Market

payment_amt

activation_date

Since_Joined

product_1

product_2

product_3

product_4

product_5

product_6

product_7

product_8

product_9

product_10

product1_PaymontAmount

product2_PaymontAmount

product3_PaymontAmount

product4_PaymontAmount

product5_PaymontAmount

product6_PaymontAmount

product7_PaymontAmount

product8_PaymontAmount

product9_PaymontAmount

product10_PaymontAmount

Remaining Amount

Julee

Data Advanture

Cubes

Android

New York

20

5/14/2011 19:25

2558.19

Cookies

Bread

Shampoo

Blanket

Door

Chair

Glass

Table

Cups

Books

13

1.5

0.8

0.6

0.5

0.3

0.2

0.2

0.1

0.12

2.68

[Anonymous]

I have millions of rows of data as given in the example attached file (input/output) tabs and I can not figure out the easy way. Could you please help.

[Anonymous]

Output table is required and input which is below in the post is table format of current data. 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Please refer to below formula to format your source data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5BD4IwDIX/CuFsgA5nwBtqYuLBi14M4VDCEhbmSgbTv+86Lh76vealr23bpjdvlEp36QVXTJrhg3b1jo2z79UStLGDIz2E7q6+yYvcFFpRBMgc9rkoABKoj0KyLWWVQc1poknH/Mkp5PRjxPdMxI5BO6mVjxI5Hh5Rs14NLhx5Ym+2F+a4IexihZKR8aEiqyIPkZtTRoo/AjNW1/0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, cust_brand = _t, Brand = _t, OS = _t, Market = _t, payment_amt = _t, activation_date = _t, Since_Joined = _t, product_1 = _t, product_2 = _t, product_3 = _t, product_4 = _t, product_5 = _t, product_6 = _t, product_7 = _t, product_8 = _t, product_9 = _t, product_10 = _t, product1_PaymontAmount = _t, product2_PaymontAmount = _t, product3_PaymontAmount = _t, product4_PaymontAmount = _t, product5_PaymontAmount = _t, product6_PaymontAmount = _t, product7_PaymontAmount = _t, product8_PaymontAmount = _t, product9_PaymontAmount = _t, product10_PaymontAmount = _t, #"Remaining Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"cust_brand", type text}, {"Brand", type text}, {"OS", type text}, {"Market", type text}, {"payment_amt", Int64.Type}, {"activation_date", type datetime}, {"Since_Joined", type number}, {"product_1", type text}, {"product_2", type text}, {"product_3", type text}, {"product_4", type text}, {"product_5", type text}, {"product_6", type text}, {"product_7", type text}, {"product_8", type text}, {"product_9", type text}, {"product_10", type text}, {"product1_PaymontAmount", Int64.Type}, {"product2_PaymontAmount", type number}, {"product3_PaymontAmount", type number}, {"product4_PaymontAmount", type number}, {"product5_PaymontAmount", type number}, {"product6_PaymontAmount", type number}, {"product7_PaymontAmount", type number}, {"product8_PaymontAmount", type number}, {"product9_PaymontAmount", type number}, {"product10_PaymontAmount", type text}, {"Remaining Amount", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"product_1", "product_2", "product_3", "product_4", "product_5", "product_6", "product_7", "product_8", "product_9", "product_10", "product1_PaymontAmount", "product2_PaymontAmount", "product3_PaymontAmount", "product4_PaymontAmount", "product5_PaymontAmount", "product6_PaymontAmount", "product7_PaymontAmount", "product8_PaymontAmount", "product9_PaymontAmount", "product10_PaymontAmount"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Mixed", each List.Transform(List.Zip({
Table.Transpose(Table.SelectColumns(#"Changed Type",List.Select(Table.ColumnNames(Source), each Text.Contains(_,"product_"))))[Column1], 
Table.Transpose(Table.SelectColumns(#"Changed Type",List.Select(Table.ColumnNames(Source), each Text.Contains(_,"_PaymontAmount"))))[Column1]}
),each Text.Combine(List.Transform(_, each Text.From(_)),","))),
    #"Expanded Product" = Table.ExpandListColumn(#"Added Custom", "Mixed"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Product", "Mixed", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Product", "Amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product", type text}, {"Amount", type number}})
in
    #"Changed Type1"

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

thank alot !

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Please refer to below formula to format your source data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5BD4IwDIX/CuFsgA5nwBtqYuLBi14M4VDCEhbmSgbTv+86Lh76vealr23bpjdvlEp36QVXTJrhg3b1jo2z79UStLGDIz2E7q6+yYvcFFpRBMgc9rkoABKoj0KyLWWVQc1poknH/Mkp5PRjxPdMxI5BO6mVjxI5Hh5Rs14NLhx5Ym+2F+a4IexihZKR8aEiqyIPkZtTRoo/AjNW1/0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, cust_brand = _t, Brand = _t, OS = _t, Market = _t, payment_amt = _t, activation_date = _t, Since_Joined = _t, product_1 = _t, product_2 = _t, product_3 = _t, product_4 = _t, product_5 = _t, product_6 = _t, product_7 = _t, product_8 = _t, product_9 = _t, product_10 = _t, product1_PaymontAmount = _t, product2_PaymontAmount = _t, product3_PaymontAmount = _t, product4_PaymontAmount = _t, product5_PaymontAmount = _t, product6_PaymontAmount = _t, product7_PaymontAmount = _t, product8_PaymontAmount = _t, product9_PaymontAmount = _t, product10_PaymontAmount = _t, #"Remaining Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"cust_brand", type text}, {"Brand", type text}, {"OS", type text}, {"Market", type text}, {"payment_amt", Int64.Type}, {"activation_date", type datetime}, {"Since_Joined", type number}, {"product_1", type text}, {"product_2", type text}, {"product_3", type text}, {"product_4", type text}, {"product_5", type text}, {"product_6", type text}, {"product_7", type text}, {"product_8", type text}, {"product_9", type text}, {"product_10", type text}, {"product1_PaymontAmount", Int64.Type}, {"product2_PaymontAmount", type number}, {"product3_PaymontAmount", type number}, {"product4_PaymontAmount", type number}, {"product5_PaymontAmount", type number}, {"product6_PaymontAmount", type number}, {"product7_PaymontAmount", type number}, {"product8_PaymontAmount", type number}, {"product9_PaymontAmount", type number}, {"product10_PaymontAmount", type text}, {"Remaining Amount", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"product_1", "product_2", "product_3", "product_4", "product_5", "product_6", "product_7", "product_8", "product_9", "product_10", "product1_PaymontAmount", "product2_PaymontAmount", "product3_PaymontAmount", "product4_PaymontAmount", "product5_PaymontAmount", "product6_PaymontAmount", "product7_PaymontAmount", "product8_PaymontAmount", "product9_PaymontAmount", "product10_PaymontAmount"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Mixed", each List.Transform(List.Zip({
Table.Transpose(Table.SelectColumns(#"Changed Type",List.Select(Table.ColumnNames(Source), each Text.Contains(_,"product_"))))[Column1], 
Table.Transpose(Table.SelectColumns(#"Changed Type",List.Select(Table.ColumnNames(Source), each Text.Contains(_,"_PaymontAmount"))))[Column1]}
),each Text.Combine(List.Transform(_, each Text.From(_)),","))),
    #"Expanded Product" = Table.ExpandListColumn(#"Added Custom", "Mixed"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Product", "Mixed", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Product", "Amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product", type text}, {"Amount", type number}})
in
    #"Changed Type1"

Regards,

Xiaoxin Sheng