Latest Enterprise DNA Initiatives

Looking for Mcode to duplicate a table with subsequent dates

HI All,

I have a table below

image

And what I am trying to do is

  1. Add a date column next to it for example 01/01/2021
  2. Then duplicate the records with the next day on the side 02/01/2021
    and so on till may be the next year.

End Result is here
image

Please see attached file below

TotalhrsCalc.xlsx (10.7 KB)

Your help greatly appreciated in advance

Thanks

Patrick

@ambepat
TotalhrsCalc.xlsx (18.8 KB)

2 Likes

Slick @AntrikshSharma :sunglasses: :sunglasses:

2 Likes

@ambepat ,

@AntrikshSharma and I were working on this at the same time, so I’ll just just post my solution for completeness.

This is the equivalent of a DAX CROSSJOIN function. Here’s an easy way to do this in M, using a custom column:

Just hit OK, expand the resulting tables and Presto! Easy crossjoin:

  • Brian

eDNA Forum - PQ Crossjoin Solution.pbix (350.6 KB)

1 Like

Awesome @BrianJ but I don’t think I got it. Can you share your file?

Thanks,

ambepat

@AntrikshSharma thanks for the solution.

@ambepat ,

Sure - file now added to my post above. I used the EDNA practice dataset tool to generate the Date table quickly, so you can just ignore all the other tables in the data model. The only two that matter are Dates (@Melissa 's extended date table) and Data (your Employee ID and Hours Worked data).

Hopefully, that will clarify things, but give a shout if you still have questions.

  • Brian

P.S. Great to see you back on the forum - it’s been a while.

@BrianJ If I understand what is happening here
= Table.AddColumn(#“Changed Type1”, “Custom”, each Table.SelectColumns( Dates, “Date” ))

It is saying for each row of my date points, select the table Dates and select column “Date”?

@ambepat,

Exactly right. This is the equivalent of

CROSSJOIN(
      Data,
     VALUES( Dates[Date] )
)
  • Brian

Hi @ambepat, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @ambepat, we’ve noticed that no response has been received from you since July 21st. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.