The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all
I'm trying to combine two tables with matching columns into a third table. The column "Item ID" contains ID numbers which may exist only in table 1 (222), only in table 2 (333), or in both table 1 and 2 (111 & 444).
The location of each item may be the same in both tables, or it may be different. So I'm trying to create "combined table" in the image below, from table 1 and table 2. Can anyone give me any pointers to acheive this?
Thanks in advance.
Thank you for the suggestion. I tried doing this originally, but found it didn't acheive the desired result. If I created a merged query (full outer), the table 2 item ID's were in a different column to the table 1 item ID's. So in my example above, the table looked like this...
I'd like to have an "Item ID" slicer, where I can filter by Item ID and see it's respective location in table 1 and table 2. In the example above, "333" isn't in the Item ID slicer for obvious reasons.
Thanks,
Hi @JW_pfsc
You can create two blank query in power query, and put the following code to Advanced Editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRclKK1YlWMjY2BrJdwGwTExMg21UpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t, #"Location " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", Int64.Type}, {"Location ", type text}})
in
#"Changed Type"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRclSK1YlWMjIyArKdwWwTExMg21UpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t, Location = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", Int64.Type}, {"Location", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item ID"}, Query1, {"Item ID"}, "Table (2)", JoinKind.FullOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Item ID", "Location "}, {"Item ID.1", "Location "}),
#"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Custom", each if [Item ID]=null then [Item ID.1] else [Item ID]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Item ID", "Item ID.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Location", "Location "}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Custom", Order.Ascending}})
in
#"Sorted Rows"
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @JW_pfsc ,
yes, you can do that through Merge queries in Power Query where you select the join full outer.
check this documentation to learn how to merge both queries https://learn.microsoft.com/en-us/power-query/merge-queries-overview
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Proud to be a Super User! | |
User | Count |
---|---|
81 | |
78 | |
37 | |
34 | |
31 |
User | Count |
---|---|
93 | |
81 | |
60 | |
49 | |
49 |