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.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors