Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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.
I just copied and pasted the script and this is what I get:
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:
@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.
The error that is shown when I click "Show Error" is let
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.
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
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.
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..
try to add a custom column using this formula:
e facce sapè
Thanks @Anonymous - That worked. Thanks
but wk57 in lines 9 and 10 is a typo or is what you want.
In the latter case, what is the logic?
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,..
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.