Forum Discussion

Query_query's avatar
Query_query
Regular Visitor
3 years ago

Using power query in powerbi to transform an excel sheet data

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 transp...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.