Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
That works! I was wondering whether this can be done using dax at the report level (not powerquery)
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |