cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmazevedo
New Member

How to Add rows to a new Custom Table based on values of a column

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:

StateRegionDaysDate
BANNE323-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:

 

StateRegionDaysDate
BANNE3

23-Jun-21

BANNE3

24-Jun-21

BANNE3

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!

 

1 ACCEPTED 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:

 

pmazevedo_1-1624575028333.png

 



1 - First, we need to turn the date columns into Int64.Type

pmazevedo_0-1624575008014.png

 


2 - Then, we have to create a custom column and build a list from this both columns:

pmazevedo_2-1624575167782.png

 

 

3 - Expand the new column:

pmazevedo_3-1624575212513.pngpmazevedo_4-1624575233435.png

 

4 - This new column will replace the first date column. 

pmazevedo_5-1624575298924.png

5 - At last, change the data type of this 2 columns and be happy!

 

pmazevedo_6-1624575364586.png

 

Thanks in advance for you support!

 

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User II
Super User II

wdx223_Daniel_0-1624504557911.png

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?

pmazevedo_1-1624541552417.png

pmazevedo_2-1624541899929.png


In the real case, I need to turn this:

GaragemRegiãoSubtraçãoRetirada RealizadaDevolução Realizada
AraçasBA308/12/202011/12/2020

 

The expected output is to increase the lines until "Retirada Realizada" became equal to "Devolução Realizada":

GaragemRegiãoSubtraçãoRetirada RealizadaDevolução Realizada
AraçasBA308/12/202011/12/2020
AraçasBA309/12/202011/12/2020
AraçasBA310/12/202011/12/2020
AraçasBA311/12/202011/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:

 

pmazevedo_1-1624575028333.png

 



1 - First, we need to turn the date columns into Int64.Type

pmazevedo_0-1624575008014.png

 


2 - Then, we have to create a custom column and build a list from this both columns:

pmazevedo_2-1624575167782.png

 

 

3 - Expand the new column:

pmazevedo_3-1624575212513.pngpmazevedo_4-1624575233435.png

 

4 - This new column will replace the first date column. 

pmazevedo_5-1624575298924.png

5 - At last, change the data type of this 2 columns and be happy!

 

pmazevedo_6-1624575364586.png

 

Thanks in advance for you support!

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors