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
Hi Community!
Me again! For me this is a puzzle:
How can I join Table1 & Table2 to come up with this:
There are some missing days in both tables what I want to happen is when they are joined(or not necessarily join as long as it can achieve the desired output), any missing days from either table will be filled with a zero.
Here's the PBIX file, it's got Table1, Table2 and the Output desired(for demo purposes).
I hope you enjoy solving my problem, I've been at it already for about 3 hours and I just can't make it happen.
Solved! Go to Solution.
I'm not quit sure if I understood your requirements and I had some problems with opening your file, but I tried to solve it.
Here is the code:
sold_items:
let Source = <YOUR_FILE_PLEASE_CHANGE_IT_TO_YOUR_PATH>, ChangedDataType = Table.TransformColumnTypes(Source,{{"date_employee", Int64.Type}, {"trans_date", type date}, {"items_sold", Int64.Type}, {"employee_id", Int64.Type}}) in ChangedDataType
pbi_prodtv_hours:
let Source = <YOUR_FILE_PLEASE_CHANGE_IT_TO_YOUR_PATH>, ChangedDataType = Table.TransformColumnTypes(Source,{{"trans_date", type date}, {"date_employee", Int64.Type}, {"employee_id", Int64.Type}, {"productive_hours", type number}}), Join = Table.NestedJoin(ChangedDataType,{"date_employee"},sold_items,{"date_employee"},"NewColumn",JoinKind.FullOuter), Expanded = Table.ExpandTableColumn(Join, "NewColumn", {"employee_id", "trans_date", "items_sold", "date_employee"}, {"employee_id.1", "trans_date.1", "items_sold", "date_employee.1"}), New_Date = Table.AddColumn(Expanded, "Date", each if [trans_date]<>null then [trans_date] else [trans_date.1]), New_Employee_ID = Table.AddColumn(New_Date, "employee_id ", each if [employee_id]<> null then [employee_id] else [employee_id.1]), Removed_Column = Table.RemoveColumns(New_Employee_ID,{"trans_date", "trans_date.1", "employee_id", "employee_id.1", "date_employee", "date_employee.1"}), Rearranged = Table.ReorderColumns(Removed_Column,{"Date", "employee_id ", "productive_hours", "items_sold"}) in Rearranged
As I said, I'm not sure if I understood your question right. Pls let me know, if this is not the solution your looking for.
Hi, a solution to this "puzzle" is:
1) Create a calendar Table and related to both tables.
2) Create a EmployeeId Table (Modeling-New Table)
EmployeeID = DISTINCT ( UNION ( VALUES ( pbi_prodtv_hours[employee_id] ), VALUES ( sold_items[employee_id] ) ) )
3) Related to both Tables:
4. Create 2 measures:
ProductiveHours = IF ( CALCULATE ( SUM ( pbi_prodtv_hours[productive_hours] ) ) <> BLANK (), CALCULATE ( SUM ( pbi_prodtv_hours[productive_hours] ) ), IF ( CALCULATE ( SUM ( sold_items[items_sold] ) ) <> BLANK (), 0 ) )
ItemSold = IF ( CALCULATE ( SUM ( sold_items[items_sold] ) ) <> BLANK (), CALCULATE ( SUM ( sold_items[items_sold] ) ), IF ( CALCULATE ( SUM ( pbi_prodtv_hours[productive_hours] ) ) <> BLANK (), 0 ) )
5. Insert a Visual Table.
What are the A_Numb er1 and A_Number2 in your PBIX File?
Hi @Anonymous & @Vvelarde, just came back from coffee break, I like see two different solutions M and DAX - I'll try them out. Thank you very much!!!
@Phil_Seamark here's the whole thing: a_number1 should be the one from the left and a_number 2 is coming from the middle but both can be interchanged, I simply wanted to know what logic would be applied to this.
The PowerQuery(M) solution was cool, but I would prefer the DAX this time only because I am more comfortable with DAX over M.
I do want to learn both though.
Hi, a solution to this "puzzle" is:
1) Create a calendar Table and related to both tables.
2) Create a EmployeeId Table (Modeling-New Table)
EmployeeID = DISTINCT ( UNION ( VALUES ( pbi_prodtv_hours[employee_id] ), VALUES ( sold_items[employee_id] ) ) )
3) Related to both Tables:
4. Create 2 measures:
ProductiveHours = IF ( CALCULATE ( SUM ( pbi_prodtv_hours[productive_hours] ) ) <> BLANK (), CALCULATE ( SUM ( pbi_prodtv_hours[productive_hours] ) ), IF ( CALCULATE ( SUM ( sold_items[items_sold] ) ) <> BLANK (), 0 ) )
ItemSold = IF ( CALCULATE ( SUM ( sold_items[items_sold] ) ) <> BLANK (), CALCULATE ( SUM ( sold_items[items_sold] ) ), IF ( CALCULATE ( SUM ( pbi_prodtv_hours[productive_hours] ) ) <> BLANK (), 0 ) )
5. Insert a Visual Table.
I'm not quit sure if I understood your requirements and I had some problems with opening your file, but I tried to solve it.
Here is the code:
sold_items:
let Source = <YOUR_FILE_PLEASE_CHANGE_IT_TO_YOUR_PATH>, ChangedDataType = Table.TransformColumnTypes(Source,{{"date_employee", Int64.Type}, {"trans_date", type date}, {"items_sold", Int64.Type}, {"employee_id", Int64.Type}}) in ChangedDataType
pbi_prodtv_hours:
let Source = <YOUR_FILE_PLEASE_CHANGE_IT_TO_YOUR_PATH>, ChangedDataType = Table.TransformColumnTypes(Source,{{"trans_date", type date}, {"date_employee", Int64.Type}, {"employee_id", Int64.Type}, {"productive_hours", type number}}), Join = Table.NestedJoin(ChangedDataType,{"date_employee"},sold_items,{"date_employee"},"NewColumn",JoinKind.FullOuter), Expanded = Table.ExpandTableColumn(Join, "NewColumn", {"employee_id", "trans_date", "items_sold", "date_employee"}, {"employee_id.1", "trans_date.1", "items_sold", "date_employee.1"}), New_Date = Table.AddColumn(Expanded, "Date", each if [trans_date]<>null then [trans_date] else [trans_date.1]), New_Employee_ID = Table.AddColumn(New_Date, "employee_id ", each if [employee_id]<> null then [employee_id] else [employee_id.1]), Removed_Column = Table.RemoveColumns(New_Employee_ID,{"trans_date", "trans_date.1", "employee_id", "employee_id.1", "date_employee", "date_employee.1"}), Rearranged = Table.ReorderColumns(Removed_Column,{"Date", "employee_id ", "productive_hours", "items_sold"}) in Rearranged
As I said, I'm not sure if I understood your question right. Pls let me know, if this is not the solution your looking for.
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.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |