Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Week Number Sequence

Hi,

 

I have different dates in my PowerBi and would like to assign the dates with Week 1, Week 2.

The dates go from 2019 to 2021, so I want them in a sequence - like after week 53, it should be week 54,55,56,...

The start date should be the first date as in the column, which I do not know too.

 

I need the dates with week numbers to be mentioned in sequential format.

Note : I am new to PowerBi too

 

sanjurk_0-1612385784791.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

try to add a custom column using this formula:

image.png

 

e facce sapè

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

Hi @Anonymous 

 

I couldnt understand what has been mentioned. Could you let me know the process, and should I enter this on editing the power query model.

Anonymous
Not applicable

I'm not sure I understand what your difficulties are. Try copying and pasting the script into the advanced editor and see if the resulting table is what you wanted. If not, explain better what you expect.

Anonymous
Not applicable

Shall I keep the column names as it is. What should I change if I put in the advanced editor

 

I had pasted it and it shows me error - token error.

 

 

Anonymous
Not applicable

 

I just copied and pasted the script and this is what I get:

 

image.png

 

If you want help, explain more precisely and show with pictures what you did and where the error arises. Attach your advanced editor

 

Questo il mio:

 

image.png

 

 

Anonymous
Not applicable

@Anonymous 

 

1. Below picture - with Error. I change "Column1" to "Date" - Could you check this, if it is right.

2. In the picture you had mentioned - will weeks go beyond 53 weeks... like 54, 55, 56.

 

Week Sequence.png

 

 

The error that is shown when I click "Show Error" is let

Anonymous
Not applicable

it looks like you did a lot of transformations before using my script. it is too difficult to tell from a picture house wrong. Certainly missing a comma, for how and where you entered the script. But even adding the comma I don't think it would be enough. To get help you must provide a starting table, even with fake data and show a table with the expected result, otherwise it is not possible to follow you. Also try to upload so that your script is copyable after cleaning it of sensitive data.

Anonymous
Not applicable

I deleted all the transofrmation and changed the script too.

 

let
    Source = Excel.Workbook(File.Contents("C:\_____"), null, true),
    CallReport05022020_Table = Source{[Item="CallReport05022020",Kind="Table"]}[Data],

Source = List.Dates(#date(2019,1,1),365*3,#duration(1,0,0,0)),
    
    #"Conversione in tabella" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    #"Modificato tipo" = Table.TransformColumnTypes(#"Conversione in tabella",{{"Date", type date}}),
    
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "wk", each if Date.Year([Date])>2019 then +52*(Date.Year([Date])-2019)+Date.WeekOfYear([Date]) else Date.WeekOfYear([Date]))
in
    #"Aggiunta colonna personalizzata"

in
    CallReport05022020_Table

 

Attached is the picture and shows error

Week Sequence-2.png

 

Anonymous
Not applicable

I'm sorry, but I can't understand what you're asking. From the changes you have made to the script, it makes me think that you are not experienced enough to use a generic script and adapt it to your situation.

Therefore, the simplest (perhaps the only?) way to get help is to provide a sample table and describe and/or show what the expected result is.

 

 

 

Anonymous
Not applicable

Start Year : 2020
Start Date : 1st January 2020
End Date : As long as the list goes

 

I am trying to attach a table, but isnt happening. I have attached a picture..Screenshot 2021-02-05 184253.png

Anonymous
Not applicable

try to add a custom column using this formula:

image.png

 

e facce sapè

Anonymous
Not applicable

Thanks @Anonymous - That worked. Thanks

Anonymous
Not applicable

but wk57 in lines 9 and 10 is a typo or is what you want.

In the latter case, what is the logic?

 

Anonymous
Not applicable

Week 57, 58, 59 -  is what I want.

Thats why in my first message I had mentioned like after week 53, it should be week 54,55,56,..

Anonymous
Not applicable

let
    Origine = List.Dates(#date(2019,1,1),365*3,#duration(1,0,0,0)),
    #"Conversione in tabella" = Table.FromList(Origine, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Conversione in tabella",{{"Column1", type date}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "wk", each if Date.Year([Column1])>2019 then +52*(Date.Year([Column1])-2019)+Date.WeekOfYear([Column1]) else Date.WeekOfYear([Column1]))
in
    #"Aggiunta colonna personalizzata"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors