Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm stuck in a business case where I need to add duplicate entries in the same (or in a new table) from inputs given in other column.
Consider I have a table with this inputs:
State | Region | Days | Date |
BA | NNE | 3 | 23-Jun-21 |
Now I want to dynamically create a custom table which will have the date being pushed to the next day nased on the Days Column. The expected Output is:
State | Region | Days | Date |
BA | NNE | 3 | 23-Jun-21 |
BA | NNE | 3 | 24-Jun-21 |
BA | NNE | 3 | 25-Jun-21 |
As the Days shows 3, 3 rows need to be pushed to the next three days.
Is this possible? Thanks for the attention!
Solved! Go to Solution.
Hi again, Daniel!
Searching the functionalities of the code sent by you in docs.microsoft, I've found a way to do this. In this case there is no need to create a Column of the "DateDifference"
Here it goes using the same example:
1 - First, we need to turn the date columns into Int64.Type
2 - Then, we have to create a custom column and build a list from this both columns:
3 - Expand the new column:
4 - This new column will replace the first date column.
5 - At last, change the data type of this 2 columns and be happy!
Thanks in advance for you support!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnJU0lHy83MFksZAbGSs61Wap2tkqBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Region = _t, Days = _t, Date = _t]),
Custom1 = #table(Table.ColumnNames(Source),List.TransformMany(Table.ToRows(Source),each List.Transform({0..Number.From(_{2})-1},(x)=>List.FirstN(_,3)&{Date.AddDays(Date.From(_{3},"en"),x)}),(x,y)=>y))
in
Custom1
Hey Daniel! Thanks for you quick answer!
You code was a sucess! But I could not use this on the real case. Since I'm no good at all in M, I was not able to understand and apply this code to my query.
Could you please help me again?
In the real case, I need to turn this:
Garagem | Região | Subtração | Retirada Realizada | Devolução Realizada |
Araças | BA | 3 | 08/12/2020 | 11/12/2020 |
The expected output is to increase the lines until "Retirada Realizada" became equal to "Devolução Realizada":
Garagem | Região | Subtração | Retirada Realizada | Devolução Realizada |
Araças | BA | 3 | 08/12/2020 | 11/12/2020 |
Araças | BA | 3 | 09/12/2020 | 11/12/2020 |
Araças | BA | 3 | 10/12/2020 | 11/12/2020 |
Araças | BA | 3 | 11/12/2020 | 11/12/2020 |
Here's the link to have access to the file: https://1drv.ms/u/s!AmPm4HbVe6W2rmw1Pg_-yRWZwAOO?e=9GxBr7
Thanks again for the support!
Hi again, Daniel!
Searching the functionalities of the code sent by you in docs.microsoft, I've found a way to do this. In this case there is no need to create a Column of the "DateDifference"
Here it goes using the same example:
1 - First, we need to turn the date columns into Int64.Type
2 - Then, we have to create a custom column and build a list from this both columns:
3 - Expand the new column:
4 - This new column will replace the first date column.
5 - At last, change the data type of this 2 columns and be happy!
Thanks in advance for you support!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.