cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stanneman
Frequent Visitor

Combining text data from 3 different databases

Hi all,

 

I am stuck with combining the data of three datasets. I've been looking around for similar cases, but haven't found what I'm looking for yet. I'm afraid I can't share the file due to the sensitivity of the data. Any help/suggestions are highly appreciated!

 

Structure of the datasets (simplified):

Transaction data. Main dataset, all the other datasets feed this dataset (single way).

- Article number (all article numbers are in this dataset)

- Sales data

 

Article description

- Article number (not all article numbers are in this dataset)

- Article name (multiple names for each article number: English, French, German, Dutch)

- Language (English, French, German, Dutch)

 

Article database

- Article number (All article numbers are in this dataset)

- Article name (all in English)

 

Goal:

What I'm trying to accomplish is to combine Transaction data [article number] with the English article names (without using filters for Language). E.g. art. nr = '12345', art. description English = 'Bread'. Combined: '12345 Bread'. I would like to put this new variable in the Transaction data database so that I can hide the other 2 databases to keep my file structured/ less messy.

 

Problem:

The English descriptions in the 'Article descriptions' database are most up-to-date and are therefore the preferred choice for the combined text-variable. Therefore, best would be to combine Transaction data [article number] with Article description [ Article name] where Article description [Language] = English. But not all article numbers are included in 'article description'. Therefore I also need to combine the text of 'Article database' for only those article numbers that are missing ór empty in the 'article description' database.

 

Most grateful for any suggestions, hope you can help this PowerBI beginner out!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Combining text data from 3 different databases

Hi @Stanneman ,

 

We can create a calculated colmun using following formula to meet your requirement:

 

Column = 
VAR n = [Article number]
VAR t1 =
    FILTER (
        'Article description',
        'Article description'[Article number] = n
            && 'Article description'[Language] = "English"
    )
VAR t2 =
    FILTER ( 'Article database', 'Transaction data'[Article number] = n )
VAR articlename =
    IF (
        COUNTROWS ( t1 ) > 0,
        MAXX ( t1, [Article name] ),
        MAXX ( t2, [Article name] )
    )
RETURN
    n & " " & articlename

13.PNG14.PNG15.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
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

6 REPLIES 6
Community Support
Community Support

Re: Combining text data from 3 different databases

Hi @Stanneman ,

 

We can create a calculated colmun using following formula to meet your requirement:

 

Column = 
VAR n = [Article number]
VAR t1 =
    FILTER (
        'Article description',
        'Article description'[Article number] = n
            && 'Article description'[Language] = "English"
    )
VAR t2 =
    FILTER ( 'Article database', 'Transaction data'[Article number] = n )
VAR articlename =
    IF (
        COUNTROWS ( t1 ) > 0,
        MAXX ( t1, [Article name] ),
        MAXX ( t2, [Article name] )
    )
RETURN
    n & " " & articlename

13.PNG14.PNG15.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
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

Stanneman
Frequent Visitor

Re: Combining text data from 3 different databases

That is very helpfull @v-lid-msft , thanks a lot for your support! Clever way to solve this. In your dataset it works like a charm, but I'm afraid I can't get it working in my own dataset.

 

The problem is caused by this part:

VAR n = [Article number]

I can't select [ Article number]. The warning I receive when manualy referring says that the column that it refers to contains too many values and no aggregation such as minimum, maximum, amount or sum is determined to get a single result. Any idea how to solve this? If I understand correctly it is because each article number is included multiple times in the article number table (which makes sense because article numbers are sold multiple times and for different prices in different periods or areas).

 

Furthermore, would it be possible to explain how the part below works? I do know how these separate functions work, but I don't get why you would use these specific commands in this particular case.

VAR articlename =
    IF (
        COUNTROWS ( t1 ) > 0,
        MAXX ( t1, [Article name] ),
        MAXX ( t2, [Article name] )
    )

Does COUNTROWS (t1) >0 determine if t1 (the 'article description' database) includes information about the article name/ is blank? And why do you need the MAXX function in this case? It is a text value, so why would a text have a max value?

Community Support
Community Support

Re: Combining text data from 3 different databases

Hi @Stanneman ,

 

The formula should works fine when add a calculated column within Transaction data Table. Even this table has multi same article number, the VAR n = [Article number] will run for row, which means get only one article number from each row.

 

the logic of article name variable is to find name in Article description Table first if there are a english name for this article number, if there are not, it will find name in the Article database Table, the purpose of using MAXX function is to get only one value from a variable table, if there are multi name in the filted variable table, it will return result by alphabeta order for text type.

 

variable table t1 is the filted Article description Table, and the variable table t1 is the filted Article database Table, all of both have only the rows contain the english article number n, so using maxx will get the name, it will get the same value using lookupvalue or value, such as following formula:

 

Column 2 =
VAR n = [Article number]
VAR nameInDescription =
    LOOKUPVALUE (
        'Article description'[Article name],
        'Article description'[Article number], n,
        'Article description'[Language], "English"
    ) & ""
VAR articlename =
    IF (
        nameInDescription <> "",
        nameInDescription,
        LOOKUPVALUE (
            'Article database'[Article name],
            'Article database'[Article number], n
        )
    )
RETURN
    n & " " & articlename


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stanneman
Frequent Visitor

Re: Combining text data from 3 different databases

Thanks for the extensive explanation @v-lid-msft, whole formula makes sense now :).

 

I'm afraid that I haven't found a solution for this though:

I can't select [ Article number]. The warning I receive when manualy referring says that the column that it refers to contains too many values and no aggregation such as minimum, maximum, amount or sum is determined to get a single result. Any idea how to solve this?


I've been trying various things, but whatever I do, I still can't select [Article number ] from my transaction table to define 'n'. In the formula field the variable appears grey and if I hold my mouse above it, powerbi says "Can't find name [Article number]". Among other things I've already tried to alter the formula by editing it to 'Transaction data'[Article number], but that didn't help. I also tried to transform the data type from 'Text' to 'Whole number' but that didn't make any difference. 

 

So close, but still not there :/. Any ideas?

 

Edit: I have two transactions that are not coupled to a specific article number due to a sytem bug. They did generate sales, but the article number cell is blank in those two transactions. Might this cause the error?

 

Edit2: If I change the first part to VAR N = MAXX('Transaction data';'Transaction data'[article number]) the error is gone and the highest article number + name is displayed correctly. Something in [article number]  seems to cause an error that prevents the formula from working.

 
Community Support
Community Support

Re: Combining text data from 3 different databases

Hi @Stanneman ,

 

Looks like you're trying to create a measure instead of a calculated column.  If you use the same exact code, but as a calculated column,  we can have the right result as following.

 

1.PNG

 

But if you are looking for a measure solution, you have correct the formula using maxx, also you can use the following code,

 

Measure = 
VAR n = MAX('Transaction data'[Article number]) VAR nameInDescription = LOOKUPVALUE ( 'Article description'[Article name], 'Article description'[Article number], n, 'Article description'[Language], "English" ) & "" VAR articlename = IF ( nameInDescription <> "", nameInDescription, LOOKUPVALUE ( 'Article database'[Article name], 'Article database'[Article number], n ) ) RETURN n & " " & articlename

 

 

2.PNG


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stanneman
Frequent Visitor

Re: Combining text data from 3 different databases

Ahhhhh, I didn't realise I was trying with the calculated measure button! Works like a charm now. Thanks for your patience and helping me out 🙂

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors