Hi All,
I hope you guys can help me with shaping my databases for PowerBI. I have two tables with me that both have a supposedly common identifier. The first table are schedules and then the second table are rows of information (e.g. clients of the day) pertaining to a certain schedule. The identifier is supposed to be constant and matched accross both tables.
Part of my long identifier (REGISTERCODE-DATE-LOCATION) is a date that is supposed to be unique and properly matching with the customer database. However, there are some server bugs that uses the day after posting date which makes it unmatched with the second database.
I already made two identifiers for the first (schedule database) the first one is the original one and the second one with the date that is the day after.
I need to find a way to create a third column of identifier that completely matches with the second database.
I tried the following:
1. Try using the relationships but failed because only one can be active at a time.
2. Try using the nested lookup function to a calculated column:
Final Identifier = IF(OR((ISERROR(LOOKUPVALUE('Tasks Export'[Identifier],
'Tasks Export'[Identifier - Copy],Schedules[Proforma Identifier]))),
(ISBLANK(LOOKUPVALUE('Tasks Export'[Identifier],
'Tasks Export'[Identifier - Copy],Schedules[Proforma Identifier])))),
LOOKUPVALUE('Tasks Export'[Identifier],'Tasks Export'[Identifier - Copy],
Schedules[Actual Identifier]),LOOKUPVALUE('Tasks Export'[Identifier],
'Tasks Export'[Identifier - Copy],Schedules[Proforma Identifier]))
3. The real purpose of me connecting the databases is to associate client transactions (2nd database) to assigned persons and schedules (1st person). I tried using merge queries but also failed as I tried all kinds of joins but failed to obtain a column that merges the identifiers.
Hope you guys can advise me on what to do. I tried to be as clear as possible. Many thanks.
