Forum Discussion
bangerasid
Helper I
Helper IPower Query - Scenario
Hi Team,
I have above scenario, I am trying to achieve the result in power query but unable to do so.
After doing Table B left join Table A, I am not getting the desired result.
The join is not taking into consideration the Record ID. Is there any way to achieve the desired result as shown in the snippet?
Happy Monday.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
CODE FOR TABLE A
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMtU31zcyMDJSitVBCBmaIIsZgsWMDKFisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record id" = _t, #"week end date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record id", Int64.Type}, {"week end date", type date}}) in #"Changed Type"CODE FOR TABLE B
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31zcyMDJSitUBcQxNkHlGhlBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"week end date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"week end date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Record id", each List.Distinct(#"Table A"[Record id])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Record id"), #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Record id", "week end date"}, #"Table A", {"Record id", "week end date"}, "Table A", JoinKind.LeftOuter), #"Expanded Table A" = Table.ExpandTableColumn(#"Merged Queries", "Table A", {"week end date"}, {"week end date.1"}), #"Sorted Rows" = Table.Sort(#"Expanded Table A",{{"Record id", Order.Ascending}, {"week end date", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "check", each if [week end date.1]=null then "absent" else "present") in #"Added Custom1"
2 Replies
- Vijay_A_Verma
Most Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
CODE FOR TABLE A
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMtU31zcyMDJSitVBCBmaIIsZgsWMDKFisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record id" = _t, #"week end date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record id", Int64.Type}, {"week end date", type date}}) in #"Changed Type"CODE FOR TABLE B
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31zcyMDJSitUBcQxNkHlGhlBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"week end date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"week end date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Record id", each List.Distinct(#"Table A"[Record id])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Record id"), #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Record id", "week end date"}, #"Table A", {"Record id", "week end date"}, "Table A", JoinKind.LeftOuter), #"Expanded Table A" = Table.ExpandTableColumn(#"Merged Queries", "Table A", {"week end date"}, {"week end date.1"}), #"Sorted Rows" = Table.Sort(#"Expanded Table A",{{"Record id", Order.Ascending}, {"week end date", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "check", each if [week end date.1]=null then "absent" else "present") in #"Added Custom1" 
bangerasidThat works! I was wondering whether this can be done using dax at the report level (not powerquery)