Add Date column from 2023 till today() without any connection for Date columns with other tables

[MianAhsan]

Hi everyone,

I'm trying to create/add a date column in a table that is already grouped by "Customer Name" and "Country" to get columns "Base" & "Run Time," as shown in the table below. I've already created a date table that is connected with all other tables, but the main table has limited date data, and that is why we are not able to get complete Week's information after performing the calculation. 

That is why I wanted to add a Date column here starting from 01.01.2023 till today(). 

Example data looks like this:

CustomerName	Country	Base	RunTime
Customer 1	US	44	44352
Customer 2	DE	95	95760
Customer 3	DE	60	60480
Customer 4	US	130	131040
Customer 5	SR	112	112896
Customer 6	US	22	22176
Customer 7	US	49	49392
Customer 8	US	48	48384
Customer 9	US	330	332640
Customer 10	US	22	22176
Customer 11	DE	35	35280
Customer 12	DE	13	13104
Customer 13	DE	16	16128
Customer 14	DE	63	63504

And I want to have results like that:

CustomerName	Country	Base	RunTime	Date
Customer 1	US	44	44352	01.01.2023
Customer 2	DE	95	95760	01.01.2023
Customer 3	DE	60	60480	01.01.2023
Customer 4	US	130	131040	01.01.2023
Customer 5	SR	112	112896	01.01.2023
Customer 6	US	22	22176	01.01.2023
Customer 7	US	49	49392	01.01.2023
Customer 8	US	48	48384	01.01.2023
Customer 9	US	330	332640	01.01.2023
Customer 10	US	22	22176	01.01.2023
Customer 11	DE	35	35280	01.01.2023
Customer 12	DE	13	13104	01.01.2023
Customer 13	DE	16	16128	01.01.2023
Customer 14	DE	63	63504	01.01.2023
Customer 1	US	44	44352	01.02.2023
Customer 2	DE	95	95760	01.02.2023
Customer 3	DE	60	60480	01.02.2023
...	...	...	...	...

Can you please help me here? I'm not an expert on PBI.

Thanks.

Regards,

Mian 

@zenisekd , @amitchandak 

[MianAhsan]

Hi @Anonymous , thank you for your response. Actually, I want to add data for those weeks where there is no runtime data, and that is why I was planning to add the Date column. Thank you for your support.

[Anonymous · Accepted solution]

Hi @MianAhsan ,

I have created a simple sample, please refer to it to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZC7DsIwDEV/BWXuED/ymoEfoOpUdeyIkHj8P4krR8QLyxnioxv7rqs7f17vx31/nsBNbpkrmAUU0G3Tj4D1+XKtKEGQoh8FUqEOGjgbgfULIC8Ez0Zp0fOtDQEP5hJHJWoKogCSEVK/pAiomEtyF7KAMo9CUYFkUSKMdlHw/9YA0D4oCND2Ab1SIO3DGL1TiILahzG4t06C0DK2Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerName = _t, Country = _t, Base = _t, RunTime = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Transform({Number.From(#date(2023,1,1))..Number.From(Date.From(DateTime.LocalNow()))},Date.From)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.