Best way to merge similar data from different sources

[Anonymous]

Hey all,

PBI Noob here again.

My next issue is I have 3 or 4 systems that capture the "same" data differently. More specifically for me its slight difference in location names. Example..

System 1	System 2	System 3
Location	Location	Location
Shed 231	North West Sydney Shed 231	Art Gallery Shed
ThePickleShop	Pickle Shop	8453 - The Pickle Shop - 544

If I wanted to manipulate/filter data based on "Shed 231" is it better to create multiple measures and then join them into one larger measure? Or is there a way to make PBI understand that North West Sydney Shed 231 = Shed 231 & Art Gallery Shed = Shed 231?

Cheers,

J

[DataZoe]

I'd also check out the fuzzy match on the merge, I used it to solve a similar problem recently.

If you have the sources in 3 different tables, you could create duplicates of each with just the locations.  Then merge with fuzzy match into a new table. That table will have three columns matching all the locations. You can bring that table into your data model, then connect up the three source tables to their matching location column. (Hide the three duplicates with only locations).  Now you can use any of the columns as a slicer in your report.

The one that was tricky with the example you gave was ThePickleShop -- it wasn't able to match until I added spaces before the capitals.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2019-feature-summary/ (it is explained a fair way down in the blog post!)

[Anonymous]

Hey Thanks for that!

I think this is probably close to what I need! What about if I needed to write a measure? If I chose 1 or any of the 3 options would they then apply to everything associated?

[DataZoe]

If you just choose one of the columns to be your slicer, it would include anything associated. 

The relationships just need to be defined:

then you can use any of the Merge1 columns to slice the other 3:

[Anonymous]

Ok cool,

So what I want/need as will is this...

Measure = COUNTROWS(Table[location])

System 1	System 2	System 3
Location	Location	Location
Shed 231	NW Sydney Shed 231	Art Gallery Shed
Shed 231	Pickle Shop	8453 - The Pickle Shop - 544
ThePickleShop	NW Sydney Shed 231	8453 - The Pickle Shop - 544
ThePickleShop	NW Sydney Shed 231	8453 - The Pickle Shop - 544
Shed 231	NW Sydney Shed 231	8453 - The Pickle Shop - 544
ThePickleShop	NW Sydney Shed 231	Art Gallery Shed

So if that was my 3 different data sets (not matched or merged/fuzzy)

I would need the output to be something like

Shed 231 = 10

ThePickleShop = 8

That makes sense?

[v-yuta-msft · Accepted solution]

@Anonymous ,

Create measure using dax like pattern below:

Count Shed 231 =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, CONTAINS ( Table1, Table1[System1], "Shed 231" ) )
)
    + CALCULATE (
        COUNTROWS ( Table2 ),
        FILTER ( Table2, CONTAINS ( Table2, Table2[System2], "Shed 231" ) )
    )
    + CALCULATE (
        COUNTROWS ( Table3 ),
        FILTER ( Table3, CONTAINS ( Table3, Table3[System3], "Shed 231" ) )
    )

Count The Pickle Shop =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        CONTAINS ( Table1, Table1[System1], "The" )
            && CONTAINS ( Table1, Table[System1], "Pickle" )
            && CONTAINS ( Table1, Table[System1], "Shop" )
    )
)
    + CALCULATE (
        COUNTROWS ( Table2 ),
        FILTER (
            Table2,
            CONTAINS ( Table2, Table2[System2], "The" )
                && CONTAINS ( Table2, Table2[System2], "Pickle" )
                && CONTAINS ( Table2, Table[System2], "Shop" )
        )
    )
    + CALCULATE (
        COUNTROWS ( Table3 ),
        FILTER (
            Table3,
            CONTAINS ( Table3, Table3[System3], "The" )
                && CONTAINS ( Table3, Table3[System3], "Pickle" )
                && CONTAINS ( Table3, Table[System3], "Shop" )
        )
    )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataZoe]

If each system was in it's own table, you could do a measure that summed a countrows for each table. Then use one of the locations in the merged table for each location:

All Rows = countrows(System1) + countrows(System2) + countrows(System3)

[amitchandak]

@Anonymous , See if this can help

https://radacad.com/append-vs-merge-in-power-bi-and-power-query