Forum Discussion

haykp's avatar
haykp
Helper I
3 years ago
Solved

How to merge 2 data sets?

Dear Community,   Please help with the first steps in PowerBi.   I have 2 excel files, which contain some device numbers and their prices. Now I want to compare these excel files. This is the co...
  • johnt75's avatar
    3 years ago

    Start off by creating a new table which has all the codes from both tables. From the Modelling tab in Power BI Desktop choose New Table and enter

    All Codes =
    DISTINCT ( UNION ( DISTINCT ( 'Table1'[Code] ), DISTINCT ( 'Table2'[Code] ) ) )
    

    Now, in the Model view delete any relationship which might have automatically been created between Table1 and Table2. Create one-to-many relationships from the All Codes table to both Table1 and Table2.

    Add 2 new columns to the All Codes table,

    Exists in Table1 =
    NOT ISEMPTY ( RELATEDTABLE ( 'Table1' ) )
    
    Exists in Table2 =
    NOT ISEMPTY ( RELATEDTABLE ( 'Table2' ) )
    

    Now you can create a measure like

    Price difference =
    AVERAGEX (
        FILTER (
            'All codes',
            'All codes'[Exists in Table1] && 'All codes'[Exists in Table2]
        ),
        VAR Table1Value =
            SUMX ( RELATEDTABLE ( 'Table1' ), 'Table1'[Price] )
        VAR Table2Value =
            SUMX ( RELATEDTABLE ( 'Table2' ), 'Table2'[Price] )
        RETURN
            Table1Value - Table2Value
    )
    

    Finally you can create table visuals with the Code column from All Codes and use filters to show those codes which exist in one table but not the other and a visual with the code and the measure showing price difference.