cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbpaux
New Member

Calculate Week number difference in header column

Hello

 

I'm receiving Excel files every weeks with some columns named for example "Feb 15", "Feb 21", etc. (5 columns having this format).

As time forwards, the first column is every week shifted so I'm not interested in the actual value but I want W+1, W+2 etc.

So I'd like to rename column names to this format.

I know the week number as it's in the filename XXXX-2021-W7.xlsx for example so I can "extract" which week number we are.

 

I'm trying to find an elegant way to do this but I'm kind of stuck here 😞

 

I was thinking to dynamically generate a table with the corresponding mapping value : first column having the date and secund the "W+1"..."W+5" and then rename them dynamically but I'm unable to do it 😄

 

Any help ?

 

Also I have a weird behavior of

 

Date.WeekOfYear(#date(2021, 01, 04), Day.Monday)

 

 

being week 2 instead of week 1 according to ISO 😕

1 ACCEPTED SOLUTION
Jimmy801
Super User III
Super User III

Hello @jbpaux 

 

It's not 100% clear what you need. But if you have a table with all column names that can be transformed in dates and you would like to use the first column as as start and rename the other with the time distance in weeks then you can use this approach. But bear in mind that you will have problems whenever you have

- other column names that are not dates and should not be considered in the renaming

- if you have another date-format (like Feb 15) you need to find a way to transform your date into a week

- when you are crossing a year, my approach will not work anymore

Here the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"15.02.21" = _t, #"22.02.21" = _t, #"02.03.21" = _t]),
    GetFirstColumnAsDate = Date.From(Table.ColumnNames(Source){0},"de-DE"),
    GetRenamedColumns = Table.TransformColumnNames
    (
        Source,
        (columnName) => if Date.From(columnName, "de-DE") = GetFirstColumnAsDate then columnName else "W+" & Text.From(Date.WeekOfYear(Date.From(columnName))-Date.WeekOfYear(GetFirstColumnAsDate))
    )
    
in
    GetRenamedColumns

before

Jimmy801_0-1613458922848.png

after

Jimmy801_1-1613458931615.png

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

4 REPLIES 4
Jimmy801
Super User III
Super User III

Hello @jbpaux 

 

It's not 100% clear what you need. But if you have a table with all column names that can be transformed in dates and you would like to use the first column as as start and rename the other with the time distance in weeks then you can use this approach. But bear in mind that you will have problems whenever you have

- other column names that are not dates and should not be considered in the renaming

- if you have another date-format (like Feb 15) you need to find a way to transform your date into a week

- when you are crossing a year, my approach will not work anymore

Here the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"15.02.21" = _t, #"22.02.21" = _t, #"02.03.21" = _t]),
    GetFirstColumnAsDate = Date.From(Table.ColumnNames(Source){0},"de-DE"),
    GetRenamedColumns = Table.TransformColumnNames
    (
        Source,
        (columnName) => if Date.From(columnName, "de-DE") = GetFirstColumnAsDate then columnName else "W+" & Text.From(Date.WeekOfYear(Date.From(columnName))-Date.WeekOfYear(GetFirstColumnAsDate))
    )
    
in
    GetRenamedColumns

before

Jimmy801_0-1613458922848.png

after

Jimmy801_1-1613458931615.png

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

jbpaux
New Member

Thank you,

It's working after some minor changes but it's kind of static.

First issue is there are other "non weeks" column before and the "weeks" column, that's why I wanted to dynamically rename the "weeks" columns like a "find & replace" of weeks column to a W+X format.

edhans
Super User III
Super User III

Again, post sample data with expected results. I know you know what you want, but I'm not going to guess again what you want and miss.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

If it is always 5 columns, you can use this simple pattern - I'm showing 3, but you get the idea. It turns this:

edhans_0-1613406817863.png

into this:

edhans_1-1613406840822.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feb 15" = _t, #"Feb 22" = _t, #"Mar 1" = _t]),
    ColumnNames = Table.ColumnNames(Source),
    #"Renamed Columns" = 
        Table.RenameColumns(
            Source,
                {
                    {ColumnNames{0}, "W1"},
                    {ColumnNames{1}, "W2"},
                    {ColumnNames{2}, "W3"}
                }
            )
in
    #"Renamed Columns"

 

The ColumnNames step returns this list:

edhans_2-1613406900113.png

By referring to ColumnNames{0} I get the first value (Power Query starts at 0, not 1), so I can dynamically rename the columns based on their position.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 

If that isn't what you want, please post sample data and expected output per instructions below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors