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 GA...
  • 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