Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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
Icon for Administrator rankAdministrator
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
    Icon for Super User rankSuper 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".