Using power query in powerbi to transform an excel sheet data

[Query_query]

Hi

I am new to power bi power query editor. Can someone please help me with transforming the data in excel format (row 1-7) to the data readable format for powerbi?

I have tried to use transpose and pivot without much success 😞

		Town	Sg	My	Vn
		Ccy	Sgd	Myr	Vnd
		Rate	1	2	3
Code	Type	Product
1	Rated	Cash	100	200	300
2	Unrated	Loan	400	500	600
3	Unrated	Bond	700	800	900
Code	Type	Product	Balance	Town	Ccy	Rate
1	Rated	Cash	100	Sg	Sgd	1
1	Rated	Cash	200	My	Myr	2
1	Rated	Cash	300	Vn	Vnd	3
2	Unrated	Loan	400	Sg	Sgd	1
2	Unrated	Loan	500	My	Myr	2
2	Unrated	Loan	600	Vn	Vnd	3
3	Unrated	Bond	700	Sg	Sgd	1
3	Unrated	Bond	800	My	Myr	2
3	Unrated	Bond	900	Vn	Vnd	3

[wdx223_Daniel]

import your data without header as Source

then input this code as Custom1

= let a=Table.ToColumns(Source),b=Table.ToRows(Source) in #table(List.FirstN(b{3},3)&{"Balance"}&List.FirstN(a{2},3),List.TransformMany(List.Skip(b,4),each List.Skip(List.Zip({_,a}),3),(x,y)=>List.FirstN(x,3)&{y{0}}&List.FirstN(y{1},3)))

[Query_query]

Thanks, this works for a table format. How can I modify the code to work with a worksheet?

[Anonymous]

Hi @Query_query 

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

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKLElNAdLOicUZQMrQwABIBqeDCZC4oVKsDlaVRmCVvpVgoggkgEulMVhlWB6YAIkbg1UaAVmheUVQtT75iSAFJtjtx6rWFLsLsKo1w+4GYxS1TvlgKXPsbsCq1gK7G7CqtcTihlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Type = _t, Product = _t, Balance = _t, Town = _t, Ccy = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Type", type text}, {"Product", type text}, {"Balance", Int64.Type}, {"Town", type text}, {"Ccy", type text}, {"Rate", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Rate", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Rate", type text}}, "en-US")[Rate]), "Rate", "Balance", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Town", "Ccy"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Code", "Type", "Product"}, {{"1", each List.Sum([1]), type nullable number}, {"2", each List.Sum([2]), type nullable number}, {"3", each List.Sum([3]), type nullable number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}})
in
    #"Changed Type1"

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.