Union : Based on condition

[Chandrashekar]

Hello,

How to Merge two tables based on condition. In attached have attached excel file

1. Sheet1 - Client List(Unique)

2. Sheet2 - Client List with Month and count details.

3. Expected result - Need to Merge Sheet1 with Sheet2 (If company not present in Sheet2 also I can able to get after Merging)

Link to the folder: Sample files 

Regards,

Chandrashekar B

[ThxAlot]

A simple join is enough,

let
    Client = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszPS1WK1YlWcoIxnGEMFxjDFcZwAzNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client List" = _t]),
    Monthly = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+7CsAgDIXhd8ksaGKjc29PIY5d2/ffWoQaOLoFPsLPKYXW577IEfvAXoLE79ZA1RXaRonaZB9FlibHRKSJdeQXZuiYCHRMFDomGTqx71HomCTodEkZOvhzzjv1BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client List" = _t, Month = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Monthly,{{"Month", type date}, {"Count", Int64.Type}}),
    #"LeftOuter Join" = Table.NestedJoin(Client, {"Client List"}, #"Changed Type", {"Client List"}, "joined", JoinKind.LeftOuter),
    #"Expanded joined" = Table.ExpandTableColumn(#"LeftOuter Join", "joined", {"Month", "Count"}),
    #"Changed Month" = let dt=List.Max(#"Changed Type"[Month]) in Table.ReplaceValue(#"Expanded joined", null, dt, Replacer.ReplaceValue, {"Month"}),
    #"Changed Count" = Table.ReplaceValue(#"Changed Month", null, 0, Replacer.ReplaceValue, {"Count"})
in
    #"Changed Count"

[Chandrashekar]

Hello,

Can you please explain how code is working. Is it possible to do with DAX?

1. Where ru refering for Table Name.

2. Is there any PDF file to learn M Language

Regards,

Chandrashekar B

[ThxAlot]

LeftOuter Join.pbix