How to merge 2 data sets?

[haykp]

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 content of the file:

Code	Price
54661-2T410	21,07
54660-2P100	21,47
54661-D3000	25,01
54651-F2010	21,59
54651-2G400	19,89
54651-2T410	21,07
54650-2P100	21,79
55311-3K050	12,13
31111-1R000	8,62
58101-1GE00	10.11
58101-2DA31	10.22

Now I have 2 these excel files and need to compare the columns/prices.

But the thing is that Code is not the same in both excels, there are missing code numbers in one excel. Also there are extra code files.

So I want to find all

1) missing codes

2) extra codes

3) same codes -> compare the prices.

Is powerBI good tool for this task?

And I imported in powerBI, and used TransformData to edit my data.

After that I got stuck, I do not know what to do next.

Any idea/hint would be very helpful please.

Thanks

Hayk

[johnt75 · Accepted solution]

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]

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]

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.