Can you provide guidance on how to perform conditional merging in Power Query (M-code) or DAX? The goal is to merge two tables based on the ID column and count the number of employees. If an employee has not used their assigned ID, the merging row should show zero, followed by the ID they used and the number of times they used it in the next row. I have provided sample data in Table 1 and Table 2, as well as the desired output result. Additionally, could you please share a Power BI file or an attached Excel sheet with the solution?
Test data is new and detailed data
to understand you can use another file
Please find the data that i am using, Please help me with merging tables with Green tab data. Merge Visit data with Customer data and try to achieve sample result data. Please help me with m-code.
When initially looking at your data my first thoughts are could this be solved through modelling, you have two tables, a dimension table and a fact table, the issue you are having is that the dimension tables granularity is different to the fact tables.
Is it possible that adding a composite key EmpID-AccessID to create a unique key in both tables, then appending the fact table to the dimension table, de deuping and only keeping the columns you need to essentially create a new dimension tablr for employees would work. You could then show items with no data and standard dax measures to show the count and include zeros?
I mocked something up in excel power pivot, essentially you have a slowly changing dimension, where you have to log the dimension over time in the dim table and create a surrogate key that you merge over in the fact table, if you are solving it through dimensional modelling.