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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ea
Regular Visitor

Determine if date is between 2 dates, return value, and turn into query

I have the following two tables:
"WkRng"
Range.PNG

and "AllDates"

File.PNG

 
I am trying to create a new column called "WkNum" in "AllDates" table but while I was successful in using the below to do it in the Data tab, I'm very new to Power BI, and am trying to turn the following into a query so that I can apply it to other similar files:

Week Number = CALCULATE(
VALUES(WkRng[Week]),
FILTER(
'WkRng',
'WkRng'[Start Date]<= EARLIER ('AllDates'[Request date])
&& 'WkRng'[End Date]>= EARLIER ('AllDates'[Request date])))
 
I was able to use the above to create column WkNum in the AllDates table in the Data tab, but I would like to try to use the Query editor so that I can apply this more readily and easily to other similar files. However, I am running into errors trying to use the above in the Custom Column editing box.
 
Again, very new to Power BI and Queries, so your advice is appreciated. I do wish to keep the use of my "WkRng" table, as the ranges are not uniform, even though the first few rows are displayed as such.
1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Hi ea you can do it like this in the query editor: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCcAwDATBXvwOyCfHklyLcf9tBAIhd8957e7dwnyad1Q716sQpahEizX6rzQwnDEYN2MygpGMYiyJ6oI8QCYgF5ANfB/nAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Request Date" = _t]), AllDates = Table.TransformColumnTypes(Source,{{"Request Date", type date}}), Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM3MtU3MjC0UNJRMtc3hDENlWJ1ooECRgg5CxjTCCpniaQPboaxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Week = _t]), WkRng = Table.TransformColumnTypes(Source2,{{"Start Date", type date}, {"End Date", type date}, {"Week", Int64.Type}}), fnGetWeekRow = (dt as date, tbl as table) as table => Table.SelectRows(tbl, each [Start Date] <= dt and [End Date] >= dt), AddedWeek = Table.AddColumn(AllDates, "WeekNum", each fnGetWeekRow([Request Date], WkRng), type table), ExpandedWeekNum = Table.ExpandTableColumn(AddedWeek, "WeekNum", {"Week"}, {"Week"}) in ExpandedWeekNum

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

1 REPLY 1
LivioLanzo
Solution Sage
Solution Sage

Hi ea you can do it like this in the query editor: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCcAwDATBXvwOyCfHklyLcf9tBAIhd8957e7dwnyad1Q716sQpahEizX6rzQwnDEYN2MygpGMYiyJ6oI8QCYgF5ANfB/nAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Request Date" = _t]), AllDates = Table.TransformColumnTypes(Source,{{"Request Date", type date}}), Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM3MtU3MjC0UNJRMtc3hDENlWJ1ooECRgg5CxjTCCpniaQPboaxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Week = _t]), WkRng = Table.TransformColumnTypes(Source2,{{"Start Date", type date}, {"End Date", type date}, {"Week", Int64.Type}}), fnGetWeekRow = (dt as date, tbl as table) as table => Table.SelectRows(tbl, each [Start Date] <= dt and [End Date] >= dt), AddedWeek = Table.AddColumn(AllDates, "WeekNum", each fnGetWeekRow([Request Date], WkRng), type table), ExpandedWeekNum = Table.ExpandTableColumn(AddedWeek, "WeekNum", {"Week"}, {"Week"}) in ExpandedWeekNum

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors