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...
Show and Tell
  • johnt75's avatar
    johnt75
    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.

johnt75's avatar
johnt75
Super User
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.

  • haykp's avatar
    haykp
    Helper I
    3 years ago

    Woww, so detail explained!

    Thank you Sir so much, I will test it.

     

    Just one more thing, usually in PowerBi users prefer to work with scirpting mode or with GUI mode? I mean is that possible to do all your explained stuff using just GUI? ( just want to know how powerbi experts work with the tool)

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      By GUI I am guessing you mean the Power Query stuff for data transformation. Speaking for myself I don't use it much as I mostly get data from database queries, and I can write the SQL myself so very little transformation is needed. On the rare occassions I need to work with Excel or some other data source the GUI can handle almost all of the transformations I need without having to write the scripts myself, which is good because I am not fantastic at M code.