Calculate Week number difference in header column

[Anonymous]

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 😕

[Jimmy801 · Accepted solution]

Hello @Anonymous 

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

after

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

[Anonymous]

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]

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.

[edhans]

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

into this:

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:

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.