cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Adding a Date column to the existing dataset

Hello,

 

I'm trying to add a date column to the dataset(the expected ouput given below).
Can you help me with the DAX expression to acheive the re

IdAmountDateADateB
2987046551610-03-202020-02-2020
29870526206.416-04-202001-04-2020
29870627005.412-05-202023-04-2020
29870767513.501-04-202017-03-2020

 

O/P Table:

 

 

Date MainIdAmountDateADateB
01-01-20202987046551610-03-202020-02-2020
01-01-202029870526206.416-04-202001-04-2020
01-01-202029870627005.412-05-202023-04-2020
01-01-202029870767513.501-04-202017-03-2020
02-01-20202987046551610-03-202020-02-2020
02-01-202029870526206.416-04-202001-04-2020
02-01-202029870627005.412-05-202023-04-2020
02-01-202029870767513.501-04-202017-03-2020
03-01-20202987046551610-03-202020-02-2020
03-01-202029870526206.416-04-202001-04-2020
03-01-202029870627005.412-05-202023-04-2020
03-01-202029870767513.501-04-202017-03-2020
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Adding a Date column to the existing dataset

@kkalyanrr 

not sure I understand correct what do you need, but maybe create new CROSSJOIN() table will help you:

New Table  = CROSSJOIN('Table', CALENDAR(DATE(2020,1,1), DATE(2020,12,31)))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: Adding a Date column to the existing dataset

@kkalyanrr 

whats rule to define your new date column?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Helper III
Helper III

Re: Adding a Date column to the existing dataset

@az38  I should start from 1 Jan 2020 to 31 Dec 2020 current calendar year.

Highlighted
Super User II
Super User II

Re: Adding a Date column to the existing dataset

@kkalyanrr 

not sure I understand correct what do you need, but maybe create new CROSSJOIN() table will help you:

New Table  = CROSSJOIN('Table', CALENDAR(DATE(2020,1,1), DATE(2020,12,31)))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Adding a Date column to the existing dataset

If you have additional transformations to do after this step, you can also do this cross join in the query editor.  Please see the M code below that does so with your example data.  Just create a blank query, go to Advanced Editor, and replace the code there with this.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5BCsAwCAT/knMMq4navqXk/99oJK1QepKR2WWvq8h5OEapxVTZ1mUQOgkEC2SBbJj1kTX+JrAWMTbCeHVwQurRKQ7o1oWg2d7/uscUV+5Nv4Ur67lszhs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Amount = _t, DateA = _t, DateB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Amount", type number}, {"DateA", type text}, {"DateB", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date Main", each List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0))),
#"Expanded Date Main" = Table.ExpandListColumn(#"Added Custom", "Date Main"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Main",{{"Date Main", type date}})
in
#"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

Highlighted
Super User IV
Super User IV

Re: Adding a Date column to the existing dataset

@kkalyanrr , refer if this file can help. This has an example to create dates between two dates. The file is attached after signature

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Adding a Date column to the existing dataset

Hi @kkalyanrr 

 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors