Forum Discussion
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 the time of selecting month 4 (in a segmenter) bring me for month 4 also data for ID 2. (Which is not in the table). Is that possible? In case you can, I would like you to do it by bringing the "amount" of the following month with data (in that case, month 6).
Thank you!
Best regards
3 Replies
Please show the expected outcome based on the sample data you provided.
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
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".
