Looking for Mcode to duplicate a table with subsequent dates

DAX DAX Calculations
1

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

2

@ambepat
TotalhrsCalc.xlsx (18.8 KB)

image
image1431Ă—612 60.6 KB

2 Likes
3

Slick @AntrikshSharma :sunglasses: :sunglasses:

2 Likes
4

@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:

image
image1486Ă—676 41.8 KB

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

Snag_19652611
Snag_19652611993Ă—457 33.6 KB

  • Brian

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

1 Like
5

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

Thanks,

ambepat

6

@AntrikshSharma thanks for the solution.

7

@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.

8

@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”?

9

@ambepat,

Exactly right. This is the equivalent of

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

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!

11

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.

12

Hi @ambepat, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!