Forum Discussion
Conditional merge columns in Query Editor or Dax
- 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
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
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