Forum Discussion
Union : Based on condition
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
3 Replies
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"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
