Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

Join(or whatever) and Fill the blanks

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).

 

join_and_fill_in_the_blanks.PNG

 

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.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @ovetteabejuela

 

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. 

View solution in original post

Vvelarde
Community Champion
Community Champion

@ovetteabejuela

 

Hi, a solution to this "puzzle" is:

 

1) Create a calendar Table and related to both tables.

 

CalendarTable-Related.png

 

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:

 

EmployeedIDTable.png

 

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.

 

Table.png




Lima - Peru

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

What are the A_Numb er1 and A_Number2 in your PBIX File?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

 

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.

 

join_and_fill_in_the_blanks_2.PNG

 

 

 

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.

Vvelarde
Community Champion
Community Champion

@ovetteabejuela

 

Hi, a solution to this "puzzle" is:

 

1) Create a calendar Table and related to both tables.

 

CalendarTable-Related.png

 

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:

 

EmployeedIDTable.png

 

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.

 

Table.png




Lima - Peru
Anonymous
Not applicable

Hi @ovetteabejuela

 

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. 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.