Forum Discussion

Niels_T's avatar
Niels_T
Icon for Post Patron rankPost Patron
5 years ago
Solved

How to change multilingual data into 1 language

Hello,

 

In my data I have the product names that share the same SKU-number but they have a different names, because of the fact that they are in different languages. They also have different orders because French customers use the French website and order through the French name, and English customers through the English name, etc.

 

Like this:

SKU           Name     orders

803141      Plate        5

803141      Bord        8

803141      Assiete    10

 

Is there a way to combine the data in a way that they are the same name and also take up only one row?

 

Like this:

 

SKU          Name     Orders
803141     Plate       23

 

Thanks in advance!

 

- N

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Niels_T ,

     

    Sorry for my late reply.

    From the screenshot you provided, it seemed that the values in Name column always contained Teller or Verre or Glas, so that you could try this:

    Table 4 =
ADDCOLUMNS (
    VALUES ( 'Table (3)'[SKU] ),
    "name",
        CALCULATE (
            MAX ( 'Table (3)'[Name] ),
            FILTER (
                ALLEXCEPT ( 'Table (3)', 'Table (3)'[SKU] ),
                CONTAINSSTRING ( 'Table (3)'[Name], "Glass" )
            )
        ),
    "orders",
        CALCULATE (
            SUM ( 'Table (3)'[orders] ),
            ALLEXCEPT ( 'Table (3)', 'Table (3)'[SKU] )
        )
)

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

     

    Please check the pbix file here.

     

    Best Regards,
    Eyelyn Qin

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Niels_T ,

     

    You could use the following formula to create a new table:

    Table 2 =
ADDCOLUMNS (
    VALUES ( 'Table'[SKU] ),
    "name", CALCULATE ( MAX ( 'Table'[Name] ), ALLEXCEPT ( 'Table', 'Table'[SKU] ) ),
    "orders", CALCULATE ( SUM ( 'Table'[orders] ), ALLEXCEPT ( 'Table', 'Table'[SKU] ) )
)

    Here is the pbix file.

     

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

     

    Best Regards,
    Eyelyn Qin

    • Niels_T's avatar
      Niels_T
      Icon for Post Patron rankPost Patron
      5 years ago

      Hello,

       

      This almost fixed my problem! 

       

      Right now I noticed that all SKU's are unique, the language however is mixed (Teller (German), Verre (French), Glas (Dutch)).

       

      Is there a way to fix that so that it is for example all Dutch?

       

       

      Best regards,

       

      Niels

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Niels_T ,

         

        Sorry for my late reply.

        From the screenshot you provided, it seemed that the values in Name column always contained Teller or Verre or Glas, so that you could try this:

        Table 4 =
ADDCOLUMNS (
    VALUES ( 'Table (3)'[SKU] ),
    "name",
        CALCULATE (
            MAX ( 'Table (3)'[Name] ),
            FILTER (
                ALLEXCEPT ( 'Table (3)', 'Table (3)'[SKU] ),
                CONTAINSSTRING ( 'Table (3)'[Name], "Glass" )
            )
        ),
    "orders",
        CALCULATE (
            SUM ( 'Table (3)'[orders] ),
            ALLEXCEPT ( 'Table (3)', 'Table (3)'[SKU] )
        )
)

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

         

        Please check the pbix file here.

         

        Best Regards,
        Eyelyn Qin

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Niels_T 

    It can be done, but we need a way to identify in the data what rows are in the language that you want (Dutch for instance). Where in your data is that information?

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers