Conditional merge columns in Query Editor or Dax

[majid_75]

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	CA	GA
CLIENT1	CA1	GA1
CLIENT2	CA1	GA1
CLIENT3	CA2	GA1
CLIENT4	CA2	GA1

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

CLIENT	CA	GA
CLIENT1	CA1	GA1
CLIENT2	CA1	GA1
CLIENT3	CA2	GA1
CLIENT4	CA2	GA1
CA1	CA1	GA1
CA2	CA2	GA1
GA1	-	GA1

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

Thanks

[camargos88]

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]

Hi Ricardo ,

I'm sorry but i have a error massage

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

[camargos88]

@majid_75 ,

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

Ricardo

[majid_75]

on the same query, this is my query:

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({
Client,
Table.Distinct(
Table.AddColumn(Table.RenameColumns(
Table.SelectColumns(Client, {"Account_Id", "Master_Account_Id"}), {"Account_Id", "RAF"}), "Account_Id", each [RAF])
),
Table.Distinct(
Table.AddColumn(Table.RenameColumns(
Table.SelectColumns(Client, {"Master_Account_Id"}), {"Master_Account_Id", "RAF"}), "Master_Account_Id", each [RAF])
)
})
in
Fonte

[camargos88 · Accepted solution]

@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

[majid_75]

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]

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

[DataVitalizer]

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 

[majid_75]

Hi @DataVitalizer ,

Yes i'am very interesting!

[DataVitalizer]

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