Merging tables with condition

[MHazib]

Hi guys,

i want to merge two tables:

Table Partners:

Table ISO Certificates:

 to get an Table like this one:

PartnerID | PartnerName | ISO 9001 | ISO 9001 Valid To | ISO 20000 | ISO 20000 Valid To | ....

38               Avaali              no              null                        no                   null

39               ELMON           no              null                        no                    null

...

44               Hewlett Pa      yes            01.03.2025             no                   null

any ideas how I am able to do that?

I tried with Table.SelectRows and List.Select but this did not worked.

Grateful for any advise!

[v-jingzhang · Accepted solution]

Hi @MHazib 

You need to first transform Table ISO Certificates into below format. 

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUfIM9lewNDAwBDINjfTN9I0MjIyB7JCgUFelWJ1oJRNUNUAFICVG2JQYmUPUGEOMMUFWYwpVY2gCUWOpb4xhjhnMHAMgALIt9A1NUd0TCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PartnerID = _t, CertificateName = _t, ValidToDate = _t, Active = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PartnerID", Int64.Type}, {"CertificateName", type text}, {"ValidToDate", type date}, {"Active", type logical}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Certificate ValidTo", each [CertificateName] & " Valid To"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Certificate ValidTo"]), "Certificate ValidTo", "ValidToDate"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[CertificateName]), "CertificateName", "Active"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column1",{"PartnerID", "ISO 9001", "ISO 9001 Valid To", "ISO 27001", "ISO 27001 Valid To", "ISO 14001", "ISO 14001 Valid To", "ISO 20000", "ISO 20000 Valid To"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"PartnerID"}, {{"All Data", each _, type table [PartnerID=nullable number, ISO 9001=nullable logical, ISO 9001 Valid To=nullable date, ISO 27001=nullable logical, ISO 27001 Valid To=nullable date, ISO 14001=nullable logical, ISO 14001 Valid To=nullable date, ISO 20000=nullable logical, ISO 20000 Valid To=nullable date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "FillDown FillUp All Data", each Table.Distinct(Table.FillUp(Table.FillDown([All Data], Table.ColumnNames([All Data])), Table.ColumnNames([All Data])))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"FillDown FillUp All Data"}),
    #"Expanded FillDown FillUp All Data" = Table.ExpandTableColumn(#"Removed Other Columns", "FillDown FillUp All Data", {"PartnerID", "ISO 9001", "ISO 9001 Valid To", "ISO 27001", "ISO 27001 Valid To", "ISO 14001", "ISO 14001 Valid To", "ISO 20000", "ISO 20000 Valid To"}, {"PartnerID", "ISO 9001", "ISO 9001 Valid To", "ISO 27001", "ISO 27001 Valid To", "ISO 14001", "ISO 14001 Valid To", "ISO 20000", "ISO 20000 Valid To"})
in
    #"Expanded FillDown FillUp All Data"

Then merge it to Table Partners.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEAaVYnWglIyAvCQTAPGMgLxkEwDwTIC8VBMA8UyAvGwSUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PartnerID = _t, PartnerName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PartnerID", Int64.Type}, {"PartnerName", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PartnerID"}, #"ISO Certificates", {"PartnerID"}, "ISO Certificates", JoinKind.LeftOuter),
    #"Expanded ISO Certificates" = Table.ExpandTableColumn(#"Merged Queries", "ISO Certificates", {"ISO 9001", "ISO 9001 Valid To", "ISO 27001", "ISO 27001 Valid To", "ISO 14001", "ISO 14001 Valid To", "ISO 20000", "ISO 20000 Valid To"}, {"ISO 9001", "ISO 9001 Valid To", "ISO 27001", "ISO 27001 Valid To", "ISO 14001", "ISO 14001 Valid To", "ISO 20000", "ISO 20000 Valid To"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ISO Certificates",{{"ISO 9001", type text}, {"ISO 27001", type text}, {"ISO 14001", type text}, {"ISO 20000", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"no",Replacer.ReplaceValue,{"ISO 9001", "ISO 27001", "ISO 14001", "ISO 20000"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","true","yes",Replacer.ReplaceText,{"ISO 9001", "ISO 27001", "ISO 14001", "ISO 20000"})
in
    #"Replaced Value1"

Sample file has been attached at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[AlexisOlson]

I'd guess you want to pivot the certificate name column in your second table before merging with the first one.