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'm searching a solution for my situation using merge with a time range, but I could not find anything about my specific situation.
I have the following data:
table_time_meal
table_used_meals
In this scenario I need to find what's the meal based on time from table_used_meals to table_time_meals. I know I could to merge by Local, add a custom column to check the time and remove the rows. The problem is that I need to keep the not found rows with a alert. I've tried to use SelectRows also, but it's too slow with the size of data I have here.
The result should be:
table_used_meals
If I merge only by Local in this example, the merge will repeat the data (because there's more than one row for each Local in table_time_meals) and I'll not be able to find the rows I should remove.
Any ideas, please?
Solved! Go to Solution.
Not sure if this will be any faster but it does create your end result from your data and does not duplicate any rows.
Note that the tables are Excel based, but this will also work in Power BI
let
Source = Excel.CurrentWorkbook(){[Name="table_time_meal"]}[Content],
table_time_meal = Table.TransformColumnTypes(Source,{
{"Local", type text}, {"Hour Start", type time}, {"Hour End", type time}, {"Meal", type text}}),
Source2 = Excel.CurrentWorkbook(){[Name="table_used_meals"]}[Content],
table_used_meals = Table.TransformColumnTypes(Source2,{
{"Local", type text}, {"Meal Time", type time}}),
join = Table.NestedJoin(table_used_meals,"Local",table_time_meal,"Local","Joined", JoinKind.FullOuter),
#"Added Custom" = Table.AddColumn(join, "Custom", each
let
Meal = Table.SelectRows([Joined], (r)=> r[Hour Start] <= [Meal Time] and r[Hour End] >= [Meal Time])[Meal]
in
if List.Count(Meal) = 0
then "Invalid"
else Meal{0}, type nullable text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Joined"})
in
#"Removed Columns"
Not sure if this will be any faster but it does create your end result from your data and does not duplicate any rows.
Note that the tables are Excel based, but this will also work in Power BI
let
Source = Excel.CurrentWorkbook(){[Name="table_time_meal"]}[Content],
table_time_meal = Table.TransformColumnTypes(Source,{
{"Local", type text}, {"Hour Start", type time}, {"Hour End", type time}, {"Meal", type text}}),
Source2 = Excel.CurrentWorkbook(){[Name="table_used_meals"]}[Content],
table_used_meals = Table.TransformColumnTypes(Source2,{
{"Local", type text}, {"Meal Time", type time}}),
join = Table.NestedJoin(table_used_meals,"Local",table_time_meal,"Local","Joined", JoinKind.FullOuter),
#"Added Custom" = Table.AddColumn(join, "Custom", each
let
Meal = Table.SelectRows([Joined], (r)=> r[Hour Start] <= [Meal Time] and r[Hour End] >= [Meal Time])[Meal]
in
if List.Count(Meal) = 0
then "Invalid"
else Meal{0}, type nullable text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Joined"})
in
#"Removed Columns"
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.