Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
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
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.
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.
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
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
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.
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
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?
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
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"
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.
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
Hi @jcbutts
I've adjusted the code to accommodate the different Column name structure.
Please see the attached file.