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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcbutts
Helper I
Helper I

Replace Column Headers with current dates

Hi,

 

I'm new to this:

 

I'm trying to replace column headers with the current week in one column and iterate from there.  For example, I have columns that say "week 1", "week 2", "week 3".....etc.  I would like to replace "week 1" with the date of the current week (start or end doesn't matter), "week 2" as a week from now, "week 3" as two weeks from now, and so on.  Is that possible?

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @jcbutts 

 

I recognized that I got you wrong. So week 1 is always the current week.

I fixed this now. Check out this one if it works

let
	Source = #table
	(
		{"week 1","week 2","week 3","other data","abc","week 52"},
		{
			{"","","","","",""}
		}
	),
    ColumnNames = Table.ColumnNames
    (
        Source
    ),
    TransformWeekInDate = List.Transform
    (
        ColumnNames,
        (row)=> try
        if Text.Contains(row,"week") or Text.Contains(row,"Week") then 
            Text.From(Date.AddDays(Date.AddDays( Date.From(DateTime.FixedLocalNow()), Date.DayOfWeek(DateTime.FixedLocalNow())*-1), (Number.From(Text.Replace(row, "week ", ""))-1)*7))
            else 
            row
        otherwise
        row
    ),
    RenameColumnHeader = Table.RenameColumns
    (
        Source,
        List.Zip({ColumnNames,TransformWeekInDate})
    )
in
	RenameColumnHeader

 

Jimmy

View solution in original post

14 REPLIES 14
Jimmy801
Community Champion
Community Champion

Hello @jcbutts 

 

this solution should be really dynamic and working for 2020. The column names have to be "week " and the number. 

let
	Source = #table
	(
		{"week 1","week 2","week 3","other data","abc","week 52"},
		{
			{"","","","","",""}
		}
	),
    ColumnNames = Table.ColumnNames
    (
        Source
    ),
    TransformWeekInDate = List.Transform
    (
        ColumnNames,
        (row)=> try
        if Text.Contains(row,"week") or Text.Contains(row,"Week") then 
            Text.From(Date.AddDays(#date(2019,12,30), (Number.From(Text.Replace(row, "week ", ""))-1)*7))
            else 
            row
        otherwise
        row
    ),
    RenameColumnHeader = Table.RenameColumns
    (
        Source,
        List.Zip({ColumnNames,TransformWeekInDate})
    )
in
	RenameColumnHeader

 

Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

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

Hi Jimmy,

 

The code seems to be what I'm looking for.  However, when I run it, "week 1"= last week (12/30/19), when it should equal this week (somewhere between 1/5/2020 and 1/11/2020).  How do I fix that?

 

Also, what are the steps to incorporate into my own code?  I apologize in advance....I'm very new at this.  I tried taking everything in your code below the "source" line and putting it in at the bottom of my code.  The result is a small matrix with a link that says "Table".  I click on that link and it reopens my data.....but the dates aren't changed.  Mine looks like this:

 

let
Source = Excel.Workbook(File.Contents("V:\\Forecast and Inventory Planning_US.xlsx"), null, true),
#"US_Forecast and Inventory Plann_Sheet" = Source{[Item="US_Forecast and Inventory Plann",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"US_Forecast and Inventory Plann_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"

 

Any direction is appreciated.  

Jimmy801
Community Champion
Community Champion

Hello @jcbutts 

 

I recognized that I got you wrong. So week 1 is always the current week.

I fixed this now. Check out this one if it works

let
	Source = #table
	(
		{"week 1","week 2","week 3","other data","abc","week 52"},
		{
			{"","","","","",""}
		}
	),
    ColumnNames = Table.ColumnNames
    (
        Source
    ),
    TransformWeekInDate = List.Transform
    (
        ColumnNames,
        (row)=> try
        if Text.Contains(row,"week") or Text.Contains(row,"Week") then 
            Text.From(Date.AddDays(Date.AddDays( Date.From(DateTime.FixedLocalNow()), Date.DayOfWeek(DateTime.FixedLocalNow())*-1), (Number.From(Text.Replace(row, "week ", ""))-1)*7))
            else 
            row
        otherwise
        row
    ),
    RenameColumnHeader = Table.RenameColumns
    (
        Source,
        List.Zip({ColumnNames,TransformWeekInDate})
    )
in
	RenameColumnHeader

 

Jimmy

Thanks, Jimmy!  That works.  I have a question on how to incorporate, if you have any pointers.  It occurred to me that I should give more details on what I'm ultimately trying to accomplish:

 

I have two tables:

 

Table1 has columns with the months of the year (as well as other columns).  The data under each column heading that has a month has the monthly forecast for the items in each row.  So something like this:

Item       Category      Description   Material     January     February   March

1               SS                  abc             123              1486          981         1547

2               SS                 xyz               456                 27           48               36

 

Table2 is a more dynamic table that gets imported every week, and has forecasts by week number (which is what you were helping me with).  It looks something like this (it has other columns too):

Item          Sales          Weight          Week 1 Forecast         Week 2 Forecast         Week 3 Forecast

1                 300                 26.3                            306                          356                          551

2                  17                  8.8                              5                               12                           15

 

Ultimately, I'm trying to compare the two forecasts.  The challenge is that one table has them by month (and is static).  The other table has it by week, but the week numbers change each week because "Week 1 Forecast" will always be the current week.  Does that make sense?  Converting those weeks to actual dates seemed like the best place to start.  My code to this point is just some rudimentary formatting steps:

 

let
Source = Excel.Workbook(File.Contents("V:\Dpmts\Forecast.xlsx"), null, true),

#"US_Forecast and Inventory Plann_Sheet" = Source{[Item="US_Forecast and Inventory Plann",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"US_Forecast and Inventory Plann_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"

 

Any direction is appreciated.

Jimmy801
Community Champion
Community Champion

Hello @jcbutts 

 

sometimes the real data is really strange and so you have to find a path to archieve certain goals.

Comparing forcast on week level to forecast on month level is almost comparing apples to pears 🙂 ... no, not that bad, but the numbers will be not that trustfully. But the only way is to bring them together. So maybe the only way to compare them is to convert week to current date, and then assign all dates from January, to the january forecast. I would also suggest to unpivot all date-value columns meaning weeks and months. Then you have a good starting point for any evaluation. Here an example of what I mean

let
	Source = #table
	(
		{"Item","Category","Description","Material","January","February","March"},
		{
			{"1","SS","abc","123","1486","981","1547"},	{"1","SS","xyz","456","27","48","36"}
		}
	),
    Unpivot = Table.UnpivotOtherColumns(Source, {"Material", "Description", "Category", "Item"}, "Attribut", "Value"),
	MonthToDate= Table.TransformColumns
	(
		Unpivot,
		{
			{
				"Attribut",
				each Date.FromText("1 " & _ & "2020","en-GB"),
				type date
			}
		}
	)
in
	MonthToDate

Have fun

Jimmy

az38
Community Champion
Community Champion

Hi @jcbutts 

try smth

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week1 = _t, Week2 = _t, Week3 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{"Week1", Text.From(Date.StartOfWeek(Date.From(DateTime.LocalNow())))}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns",{"Week2", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),1)))})
in
    #"Renamed Columns2"

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Thanks.  I already have some modifications prior to this step, so how do I change your code to accept the existing source?  I get an error in the advanced editor saying the variable named 'source' is already defined.

az38
Community Champion
Community Champion

@jcbutts 

you should replace source here

 #"Renamed Columns" = Table.RenameColumns(Source,{"Week1", Text.From(Date.StartOfWeek(Date.From(DateTime.LocalNow())))}),

to the name of your previous step,like you could see here (#"Renamed Columns" is a name of previous step)

    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns",{"Week2", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),1)))})

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi Az38,

 

Sorry, that's not making sense to me.  My code looks like this:

 

let
Source = Excel.Workbook(File.Contents("V:\Dpmts\Forecast.xlsx"), null, true),
#"US_Forecast and Inventory Plann_Sheet" = Source{[Item="US_Forecast and Inventory Plann",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"US_Forecast and Inventory Plann_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ASIN", type text}})
in
#"Changed Type"

 

I assume I need to keep the line calling out source= my excel workbook.  Do I put in a new "let"-> "in" sequence or does your code get nested in to what I have?

az38
Community Champion
Community Champion

@jcbutts 

try

let
Source = Excel.Workbook(File.Contents("V:\Dpmts\Forecast.xlsx"), null, true),
#"US_Forecast and Inventory Plann_Sheet" = Source{[Item="US_Forecast and Inventory Plann",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"US_Forecast and Inventory Plann_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ASIN", type text}}),
    #"Renamed Columns" = Table.RenameColumns(Source,{"Week1", Text.From(Date.StartOfWeek(Date.From(DateTime.LocalNow())))}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns",{"Week2", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),1)))}),
    #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{"Week3", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),2)))})
in
    #"Renamed Columns3"

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Mariusz
Community Champion
Community Champion

Hi @jcbutts 

 

You can try this as an alternative.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"week 1" = _t, #"week 2" = _t, #"week 3" = _t]),
    transformColumnNames = List.Transform( 
        List.Select( 
            Table.ColumnNames( Source ), 
            ( i ) => Text.Contains( i, "week" ) 
        ), 
        (i) => let 
            extractWeek = Number.From( Text.Replace( i, "week ", "" ) ),
            weekStart = Date.StartOfWeek( DateTime.LocalNow() ),
            newName = 
                Text.From( 
                    Date.From( 
                        Date.AddWeeks( weekStart, extractWeek - 1 ) 
                    ) 
                )
        in  
            { i, newName }
    ),
    #"Renamed Columns" = Table.RenameColumns(Source, transformColumnNames )
in
    #"Renamed Columns"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

I tried all of these.  For the first solution, I got an error saying the colunmn "Week 1" wasn't found.  My actual column header is "Week 1- Mean Forecast" so I modified the code to match (checking a few different iterations for the spacing between the dash and the words) but it all came up with the same error...."Week 1 - Mean Forecast" not found.

 

For the other two solutions, the code was accepted, but it resulted in a table with one line.   The headers were things like "Name" and "Hidden", and the data was just the name of my data file and the type.

Jimmy801
Community Champion
Community Champion

Hello @jcbutts 

 

in my solution I've stated that the code is to show you how the solution works, nothing more, and that is has to be adapted to your query by copying and pasting or creating a customer function. It wasn't stated in your first request that we can have a negative number. I've proposed a dynamic solution for year 2020 considering that the week 1 is the one starting on 30.12.19 exactly how it was requested

 

Jimmy

Mariusz
Community Champion
Community Champion

Hi @jcbutts 

 

I've adjusted the code to accommodate the different Column name structure.

 

Please see the attached file.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors