Forum Discussion

Corlin's avatar
Corlin
Frequent Visitor
7 years ago
Solved

Field mismatch when using UNION

I am trying to combine two tables, but when I try to union them I get a mismatch between the fields as seen below.
The goal is to create a table that contains all dates and filling the empty fields with zeroes.

The calendar contains "dates"
WFOpenClosed contains "dates", "Open", "Closed", "WorkflowID"

I have been trying to follow this implementation.
Any help with my problem would be greatly appreciated.

I am using the following code to populate the table:

WFOpenClosedZeroFilled = 
UNION (
    WFOpenClosed;
    ADDCOLUMNS (
        EXCEPT ( VALUES ( 'calendar'[Date] ); VALUES ( WFOpenClosed[Date] ) );
        "Open"; 0;
        "Closed"; 0;
        "WorkflowID"; 0
    )
)


Closed and Date is swapped in the union.

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Corlin

     

    You may have a look at UNION Function. To get the table as requested, the first way is to make sure the 'Date' column in WFOpenClosed table is the first column as below. Then you may get the table with your code.

     

    The second way is to change the code like below to get the table as requested.

    WFOpenClosedZeroFilled =
UNION (
    SELECTCOLUMNS (
        WFOpenClosed,
        "date", WFOpenClosed[date],
        "Closed", WFOpenClosed[Closed],
        "WorkflowID", WFOpenClosed[WorkflowID],
        "Open", WFOpenClosed[Open]
    ),
    ADDCOLUMNS (
        EXCEPT ( VALUES ( 'calendar'[Date] ), VALUES ( WFOpenClosed[Date] ) ),
        "Closed", 0,
        "Open", 0,
        "WorkflowID", 0
    )
)

    Regards,

    Cherie

     

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Corlin Please post the sample input data and your expected output as well, which will be helpful to understand and resolve your issue quickly.....

    • Corlin's avatar
      Corlin
      Frequent Visitor
      7 years ago

      My input data is the bottom half of the image.
      The value seen in the Date column in the bottom half is decimal number corresponding to a date, and as you can see are the upper half of the rows inputted wrong as the date gets in the Closed column and the Closed value in the Date column

      What I want to achieve is inserting 0 for the closed, open and WorkflowID for the days nothing were logged as seen below.



  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Corlin

     

    You may have a look at UNION Function. To get the table as requested, the first way is to make sure the 'Date' column in WFOpenClosed table is the first column as below. Then you may get the table with your code.

     

    The second way is to change the code like below to get the table as requested.

    WFOpenClosedZeroFilled =
UNION (
    SELECTCOLUMNS (
        WFOpenClosed,
        "date", WFOpenClosed[date],
        "Closed", WFOpenClosed[Closed],
        "WorkflowID", WFOpenClosed[WorkflowID],
        "Open", WFOpenClosed[Open]
    ),
    ADDCOLUMNS (
        EXCEPT ( VALUES ( 'calendar'[Date] ), VALUES ( WFOpenClosed[Date] ) ),
        "Closed", 0,
        "Open", 0,
        "WorkflowID", 0
    )
)

    Regards,

    Cherie

     

    • Corlin's avatar
      Corlin
      Frequent Visitor
      7 years ago

      Thank you for your input, I ended up using the SELECTCOLUMN to solve my problem.