Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Data query

Nice day. I need help I have a table like this My ID Amount 1 1 100 2 1 200 3 1 300 4 1 400 5 1 500 6 1 600 3 2 5000 6 2 6000 I need that at...
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Thank you for your reply.

In this case, I would like you to bring the value 6000 for ID 2. And bring me the value 400 for ID 1.

In the case of selecting month 2, bring me the value 5000 for ID 2 and the value 200 for ID 1.

Is it understood?

Thank you.

Best regards

lbendlin's avatar
lbendlin
Super User
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWADA6VYnWglIyjfCMo3hvKNoXwTKN8EyjeF8k2hfDMo3wxJvxFEHqHACKIAKBALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [My = _t, ID = _t, Amount = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[ID]), "ID", "Amount"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Up", {"My"}, "ID", "Amount"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Amount", Currency.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".