topic Re: Union : Based on condition in DAX Commands and Tips

Union : Based on condition

Chandrashekar — Thu, 27 Apr 2023 06:17:18 GMT

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

Re: Union : Based on condition

ThxAlot — Thu, 27 Apr 2023 10:05:37 GMT

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"

Re: Union : Based on condition

Chandrashekar — Thu, 27 Apr 2023 12:16:20 GMT

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

Re: Union : Based on condition

ThxAlot — Thu, 27 Apr 2023 13:00:03 GMT

LeftOuter Join.pbix