Using power query in powerbi to transform an excel sheet data

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.

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)))