Combining text data from 3 different databases

[Anonymous]

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!

[Anonymous]

@v-lid-msft , I've got a bit similar problem as last year. Would you be able to help me out again?
I now have a column with 'art. number + name'  and a column with 'language code'.
Example:

Art.nr+name        LanguageCode

12345 Bread        EN
12345 Brot           DE
12345 Brood        NL

I have 2 problems:
1. I would like to split the first column at the first space in 2 columns: Article number; Article name (I don't have a separate article name column now, but can be very usefull for various tables)

2. I would like to make a new column with (1x each unique) art.number & 1 language of choice.

E.g. if I filter for English, only 12345 Bread remains from this list (and all the unique other codes + english description). 

I've been trying, but without succes. Can you give me a hand again? Would be much appreciated!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

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

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.

[Anonymous]

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?

[v-lid-msft]

Hi @Anonymous ,

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.

[Anonymous]

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.

[v-lid-msft]

Hi @Anonymous ,

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.

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

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.

[Anonymous]

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 🙂