Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am getting a token eof error on this query and I am not sure why. The week variable prints 26 when I run this with out the database part.
let
week = Date.WeekOfYear(DateTime.LocalNow())
in
week
let
Source = Odbc.DataSource("dsn=SystemDSN_ELK_PROD", [HierarchicalNavigation=true]),
gsec_Database = Source{[Name="gsec",Kind="Database"]}[Data],
#"data-resilient_Table" = gsec_Database{[Name="data-resilient-all-2020week",Kind="Table"]}[Data]
in
#"data-resilient_Table"
Solved! Go to Solution.
were you planning to use the week in the calculation further below?
power query can only return one result - you asked it to return two.
OK- I need to use the weeknumber in the table name. I knocked it down and now I get this:
DataSource.Error: The table has no visible columns and cannot be queried.
Details:
data-resilient-all-2020week
Show your new Power Query?
let
week = Date.WeekOfYear(DateTime.LocalNow()),
Source = Odbc.DataSource("dsn=SystemDSN_ELK_PROD", [HierarchicalNavigation=true]),
gsec_Database = Source{[Name="gsec",Kind="Database"]}[Data],
#"data-resilient-all-2020week_Table" = gsec_Database{[Name="data-resilient-all-2020week",Kind="Table"]}[Data]
in
#"data-resilient-all-2020week_Table"
You're still not using the week. Maybe you wanted to do this?
let
week = Date.WeekOfYear(DateTime.LocalNow()),
Source = Odbc.DataSource("dsn=SystemDSN_ELK_PROD", [HierarchicalNavigation=true]),
gsec_Database = Source{[Name="gsec",Kind="Database"]}[Data],
#"data-resilient-all-2020week_Table" = gsec_Database{[Name="data-resilient-all-2020" & week,Kind="Table"]}[Data]
in
#"data-resilient-all-2020week_Table"
warmer, How do I concatentate 2020 and week? It needs to look like data-resilient-all-202026
Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=data-resilient-all-2020
Right=26
Welcome to my first day with Power M. This works:
let
week = Date.WeekOfYear(DateTime.LocalNow()),
strWeek = Number.ToText(week),
Source = Odbc.DataSource("dsn=SystemDSN_ELK_PROD", [HierarchicalNavigation=true]),
gsec_Database = Source{[Name="gsec",Kind="Database"]}[Data],
#"data-resilient_Table" = gsec_Database{[Name="data-resilient-all-2020"&strWeek,Kind="Table"]}[Data]
in
#"data-resilient_Table"
Now what will your query do on Jan 1st 2021? 🙂
crash and burn. I think I can figure out the year. we will see.. I wanted to get this working first to minimize the number of problems I had at one time!!
Thanks for your help!
Right, i forgot your week is a number.
Which brings up the next question - are weeks 1 to 9 supposed to come over with one digit or two digits? Ie "2" or "02" for week 2?
Assuming "02" you would do
Number.ToText(week,"D2")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.