Forum Discussion
PowerBI Model time dependant
Hi PGG79 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin and SundarRaj for the response.
I have worked on your sample data and attached the PBIX file with the results. Please review it and let us know if it is helpful. If you have any further questions, please provide more details.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
Hi PGG79 , is this what you are looking for? I'll attach the images of the output and M code used. Let me know if I understood your query correctly. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name = "Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician", type text}, {"Recording date", type date}, {"Hours recorded", Int64.Type}}),
LookUp = Table.AddColumn(#"Changed Type","LookUpVal",each [Technician] & "-" & Text.From([Recording date])),
Table = Excel.CurrentWorkbook(){[Name = "Table6"]}[Content],
Type = Table.TransformColumnTypes(Table,{{"Technician", type text}, {"Working Schedule", type text}, {"Active since", type date}}),
Position = Table.AddColumn(Type,"HoursRecorded",each List.PositionOf(LookUp[LookUpVal],[Technician] & "-" & Text.From([Active since]))),
#"Recorded Hours" = Table.TransformColumns(Position,{"HoursRecorded", each try LookUp{_}[Hours recorded] otherwise null})
in
#"Recorded Hours"
Hi SundarRaj
thank you for the quick answer and for the idea, but that solution is based on a vlookup and that means that if the match of "technician+startdate" with "technician+recording date" is not exact, the result is "null". Please check the answer i have given to Ibendlin that contains more details because when i did the first post it was a shorten example but the tables have more fields. Thank you for your support!
