Extract the Week Number and Year from Serial Number (whole number)

[aBedin]

Hello Community!

I am wrapping my head around this...

My serial number is composed by AA-WW-XXX:
AA = Year

WW = Week number

XXX = ID

Example:

1345007

1345008

...

2406356

I was able to extract the two data from my column "Serial Number" through Power Query (see pic. below) but I do not sure if it is the right way because I would like to create a Line chart in Report View and set on the X-axis the Granularity "Year - Week Number". 

I notice that the Week Number become "Whole number" instead of "Date" and when I try to change it manually it messes up everything.

For me it seems the Year data and the Week number are not somehow "connect" each other..

Line Chart (WRONG)

Thanks to all

Andrew

[aBedin]

Dear @bhanu_gautam ,

I am sure your code is valid because It works but unfortanetly I got an error back in Power Query column.

I do not know M code and that is hard for me do an analysis of errors.

I will follow other ways to achieve the resolution of my problem if you have any.

If not, I will try to study your code and see I am able to resolve.

Thanks for your support

Andrew

[Anonymous]

Hi @aBedin ,

You could share your error message in Power Query column with us here, or provide a screenshot. You could also provide example data or sample files here to offer you more help. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Please don't forget to accept bhanu_gautam's reply as a solution if it has already helped you solve your problem! 

This will make it easier for other users who may be experiencing the same problem to find a solution.

Best regards,

Lucy Chen

[aBedin]

Dear @bhanu_gautam ,

I am sure your code is valid because It works but unfortanetly I got an error back in Power Query column.

I do not know M code and that is hard for me do an analysis of errors.

I will follow other ways to achieve the resolution of my problem if you have any.

If not, I will try to study your code and see I am able to resolve.

Thanks for your support

Andrew

[bhanu_gautam · Accepted solution]

@aBedin , Try using below m query

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddYearWeek = Table.AddColumn(Source, "YearWeek", each let
SerialNumber = [SerialNumber],
Year = Number.RoundDown(SerialNumber / 100000),
WeekNumber = Number.RoundDown((SerialNumber - (Year * 100000)) / 1000)
in
[Year, WeekNumber]),
AddDate = Table.AddColumn(AddYearWeek, "Date", each let
Year = [YearWeek][Year],
WeekNumber = [YearWeek][WeekNumber],
StartOfYear = #date(Year, 1, 1),
Date = Date.AddWeeks(StartOfYear, WeekNumber - 1)
in
Date)
in
AddDate