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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
aBedin
New Member

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

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..

Serial number to Week and Year.jpg

 

Line Chart (WRONG)

Line chart (Wrong).pngLine Chart - X e Y data.jpg

 

 

 

Thanks to all

Andrew

4 REPLIES 4
aBedin
New Member

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

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
New Member

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
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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