Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 
Customercust_brandBrandOSMarketpayment_amtactivation_dateSince_JoinedProductsAmount
JuleeData AdvantureCubesAndroidNew York20########2558.32Cookies13
JuleeData AdvantureCubesAndroidNew York20########2558.32Bread1.5
JuleeData AdvantureCubesAndroidNew York20########2558.32Shampoo0.8
JuleeData AdvantureCubesAndroidNew York20########2558.32Blanket0.6
JuleeData AdvantureCubesAndroidNew York20########2558.32Door0.5
JuleeData AdvantureCubesAndroidNew York20########2558.32Chair0.3
JuleeData AdvantureCubesAndroidNew York20########2558.32Glass0.2
JuleeData AdvantureCubesAndroidNew York20########2558.32Table0.2
JuleeData AdvantureCubesAndroidNew York20########2558.32Cups0.1
JuleeData AdvantureCubesAndroidNew York20########2558.32Books0.12
JuleeData AdvantureCubesAndroidNew York20########2558.32Others2.68
Input
 
Customercust_brandBrandOSMarketpayment_amtactivation_dateSince_Joinedproduct_1product_2product_3product_4product_5product_6product_7product_8product_9product_10product1_PaymontAmountproduct2_PaymontAmountproduct3_PaymontAmountproduct4_PaymontAmountproduct5_PaymontAmountproduct6_PaymontAmountproduct7_PaymontAmountproduct8_PaymontAmountproduct9_PaymontAmountproduct10_PaymontAmountRemaining Amount
JuleeData AdvantureCubesAndroidNew York205/14/2011 19:252558.19CookiesBreadShampooBlanketDoorChairGlassTableCupsBooks131.50.80.60.50.30.20.20.10.12

2.68

 

 

 

 

 

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

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"

18.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Anonymous
Not applicable

2 REPLIES 2
Anonymous
Not applicable

thank alot !

v-shex-msft
Community Support
Community Support

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"

18.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.