Forum Discussion

majid_75's avatar
majid_75
Helper I
6 years ago
Solved

Conditional merge columns in Query Editor or Dax

Hi !

 

Let consider 4 Clients:

- Client1, Client2, Client3, Client4

  • Client1 and Client2 belong to the Company CA1
  • Client3 and Client4 belong to the Company CA2
    • CA1 and CA2 belong to a Group GA1

I have thise table:

CLIENT

CAGA

CLIENT1

CA1GA1

CLIENT2

CA1GA1

CLIENT3

CA2GA1

CLIENT4

CA2GA1

At the end, i want to column with a referential Client:

 

CLIENT

CAGA

CLIENT1

CA1GA1

CLIENT2

CA1GA1

CLIENT3

CA2GA1

CLIENT4

CA2GA1

CA1

CA1GA1

CA2

CA2GA1

GA1

-GA1

 

Please help me, i don't know if the better is power query or dax.

 

Thanks

 

Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    majid_75 ,

     

    let
    Source = Excel.Workbook(File.Contents("C:\....\client_referentiel.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Account_Id", type text}, {"Account_Name", type text}, {"Account_Name_Real", type text}, {"Account_Platform", type text}, {"Account_Type", type text}, {"Entity_Category", type text}, {"Entity_Country_Origin", type text}, {"Entity_Country_Risk", type text}, {"Entity_HoG_Pivot_Name", type text}, {"Entity_Id", type text}, {"Entity_Name", type text}, {"Entity_Sector", type text}, {"Entity_ShortName_Mnem", type text}, {"Entity_Strategic_Sector", type text}, {"Entity_Strategic_SubSector", type text}, {"Entity_SubCategory", type text}, {"Flag_Group", type text}, {"isSRM_Account", type text}, {"Master_Account", type text}, {"Master_Account_Id", type text}, {"Master_Account_Real", type text}, {"RAF", Int64.Type}, {"Tiers_Type", type text}}),
    Fonte =
    Table.Combine({
    #"Type modifié" ,
    Table.Distinct(
    Table.AddColumn(Table.RenameColumns(
    Table.SelectColumns(#"Type modifié" , {"Account_Id", "Master_Account_Id"}), {"Account_Id", "RAF"}), "Account_Id", each [RAF])
    ),
    Table.Distinct(
    Table.AddColumn(Table.RenameColumns(
    Table.SelectColumns(#"Type modifié" , {"Master_Account_Id"}), {"Master_Account_Id", "RAF"}), "Master_Account_Id", each [RAF])
    )
    })
    in
    Fonte

     

     

10 Replies

  • DataVitalizer's avatar
    DataVitalizer
    Super User
    6 years ago

    Hi majid_75 

    You can use Power Query to get the preview, is it possible with DAX actually I have no idea for this moment.

    If you opt for Power query then you will have to go through different steps to end up with that preview.

     

    If intereseted let me know 🙂

     

    Does it work ? Mark it as a solution 

     

     

      • DataVitalizer's avatar
        DataVitalizer
        Super User
        6 years ago

        Hi majid_75 

        Make sure that the steps I will share with you are longer than the script camargos88  has shared.

         

        Here below the steps you can follow to get that result:

        1- duplicate your table 3 times and in each table you will keep only one column: Table1 Client , Table 2 CA, Table 3 GA

        2- Rename the columns in the 3 tables in order to append them in a new table with 1 column

        3- you will end up with 1 table and 1 column containing all your data then you have to remove duplicates 

         

        Once you finish the 3rd step let me know so you don't get lost 🙂

        Does it work ? Mark it as a solution 

         

         

         

         

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi majid_75 ,

     

    You can try this m code:

     

    let
    Fonte =
    Table.Combine({
    Tabela,
    Table.Distinct(
    Table.AddColumn(Table.RenameColumns(
    Table.SelectColumns(Tabela, {"CA", "GA"}), {"CA", "CLIENT"}), "CA", each [CLIENT])
    ),
    Table.Distinct(
    Table.AddColumn(Table.RenameColumns(
    Table.SelectColumns(Tabela, {"GA"}), {"GA", "CLIENT"}), "GA", each [CLIENT])
    )
    })
    in
    Fonte

     

    Did I answer your question? Mark my post as a solution!
    Ricardo

    • majid_75's avatar
      majid_75
      Helper I
      6 years ago

      Hi camargos88 ,

       

      Thanks you very much but it's not good. I think a mistake with

      let
      Fonte =
      Table.Combine({
      Tabela

       

      I replaced Tabela by the name of my table (Client) but it doesn't work

       

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Hi majid_75 ,

         

        "Tabela" is your original table name.

         

        Just replace it with in every part.

         

        Did I answer your question? Mark my post as a solution!
        Ricardo

    • majid_75's avatar
      majid_75
      Helper I
      6 years ago

      Hi Ricardo ,

       

      I'm sorry but i have a error massage

      Expression.Error: A cyclical reference was detected during the evaluation

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        majid_75 ,

         

        Did you create a blank query or are you trying to apply it on the same query ?

         

        Ricardo