cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Best way to merge similar data from different sources

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 1System 2System 3
LocationLocationLocation
Shed 231North West Sydney Shed 231Art Gallery Shed
ThePickleShopPickle Shop8453 - 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Jaydpie ,

 

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.

 

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

@Jaydpie , See if this can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User I
Super User I

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.

MergedTable.JPGMergeSetup.JPG

 

 

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!)

Highlighted

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?

Highlighted

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

 

The relationships just need to be defined:

MergedDataModel.JPG

 

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

MergedSlicer.JPG

Highlighted

Ok cool,

 

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

Measure = COUNTROWS(Table[location])

System 1System 2System 3
LocationLocationLocation
Shed 231NW Sydney Shed 231Art Gallery Shed
Shed 231Pickle Shop8453 - The Pickle Shop - 544
ThePickleShop NW Sydney Shed 2318453 - The Pickle Shop - 544
ThePickleShop NW Sydney Shed 2318453 - The Pickle Shop - 544
Shed 231 NW Sydney Shed 2318453 - The Pickle Shop - 544
ThePickleShop NW Sydney Shed 231Art 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?

Highlighted

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)

Highlighted

@Jaydpie ,

 

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.

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors