Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I´m trying to do this,
Given a table with orders:
And another one with the stock to satisfy those orders:
I´d like to paste them together by [Type] like this:
So that for every [Type] it can be compared the orders with the stock. For this, when the join happens, new rows in each table should not be duplicates but empty.
My idea would be to do a full join and then, after the first instance of every value of [PO] and [ID] (in red in the picture below), replace them for null. So I can shape the table the way I need.
Any ideas on how to do this?
Best regards.
Solved! Go to Solution.
Hi @Anonymous
Create "rank" and "merged" column in "Orders" and "Stock" table,
Then merge two tables based on "Merged1"and "Merged2" columns,
Hi @Anonymous
Create "rank" and "merged" column in "Orders" and "Stock" table,
Then merge two tables based on "Merged1"and "Merged2" columns,
That´s a great idea @v-juanli-msft , thanks! I´ll try now this approach with production data, hope it works as well.
I´ll paste the code here for future reference. It´s the same but with a little tweak to order the table as I need.
PO_ORDER:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyVIrVATKNEExjINMJwjSBMGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PO = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}, {"Type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"AllRows", each _, type table [PO=text, Type=text]}}),
RankFunction = (#"Changed Type" as table) as table =>
let
SortRows = Table.Sort(#"Changed Type", {{"PO", Order.Ascending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex,
AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RankFunction(_)}),
#"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"PO", "Rank"}, {"AllRows.PO", "AllRows.Rank"}),
#"Rename Columns" = Table.RenameColumns(#"Expanded AllRows", {{"AllRows.PO", "PO"}, {"AllRows.Rank", "Rank"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Rename Columns", "Merged1", each Text.Combine({[Type], Text.From([Rank], "en-US")}, "_")),
#"Added Index" = Table.AddIndexColumn(#"Inserted Merged Column", "Index", 1, 1),
#"Grouped Rows1" = Table.Group(#"Added Index", {"Type"}, {{"MaxIndex", each List.Max([Index]), type number}}),
#"Inserted Addition" = Table.AddColumn(#"Grouped Rows1", "Addition", each [MaxIndex] + 0.5, type number)
in
#"Inserted Addition"
Stock:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jFU0lFyVIrVATKNEExjBNMEwTQFMp2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"AllRows", each _, type table [PO=text, Type=text]}}),
RankFunction = (#"Changed Type" as table) as table =>
let
SortRows = Table.Sort(#"Changed Type", {{"ID", Order.Ascending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex,
AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RankFunction(_)}),
#"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"ID", "Rank"}, {"AllRows.ID", "AllRows.Rank"}),
#"Rename Columns" = Table.RenameColumns(#"Expanded AllRows", {{"AllRows.ID", "ID"}, {"AllRows.Rank", "Rank"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Rename Columns", "Merged2", each Text.Combine({[Type], Text.From([Rank], "en-US")}, "_"))
in
#"Inserted Merged Column"
PO:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyVIrVATKNEExjINMJwjSBMGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PO = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}, {"Type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"AllRows", each _, type table [PO=text, Type=text]}}),
RankFunction = (#"Changed Type" as table) as table =>
let
SortRows = Table.Sort(#"Changed Type", {{"PO", Order.Ascending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex,
AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RankFunction(_)}),
#"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"PO", "Rank"}, {"AllRows.PO", "AllRows.Rank"}),
#"Rename Columns" = Table.RenameColumns(#"Expanded AllRows", {{"AllRows.PO", "PO"}, {"AllRows.Rank", "Rank"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Rename Columns", "Merged1", each Text.Combine({[Type], Text.From([Rank], "en-US")}, "_")),
#"Added Index" = Table.AddIndexColumn(#"Inserted Merged Column", "Index", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Merged1"}, Stock, {"Merged2"}, "Stock", JoinKind.FullOuter),
#"Expanded Stock" = Table.ExpandTableColumn(#"Merged Queries", "Stock", {"Type", "ID", "Rank", "Merged2"}, {"Stock.Type", "Stock.ID", "Stock.Rank", "Stock.Merged2"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Stock", {"Stock.Type"}, PO_ORDER, {"Type"}, "PO_ORDER", JoinKind.LeftOuter),
#"Expanded PO_ORDER" = Table.ExpandTableColumn(#"Merged Queries1", "PO_ORDER", {"Addition"}, {"PO_ORDER.Addition"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded PO_ORDER",null,each [PO_ORDER.Addition],Replacer.ReplaceValue,{"Index"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Index", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Type", "PO", "Index", "Stock.ID", "Stock.Type"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"PO", "Type", "Stock.ID", "Stock.Type", "Index"})
in
#"Reordered Columns"
Hi @Anonymous ,
I would create the column ID with nulls in the orders table, then make sure both "Type" columns have the exact same name (spaces are important) and then append.
Let me know if it worked and if so mark as sollution.
BR,
DR
Hi @Anonymous, I don´t understand what you mean by "create the column ID with nulls in the orders table". Could you be more specific please?
Best regards.
I think I missunderstood what you wanted.
Can you specify with a file with no relevant data?
I think you're trying to join or merge by Type but Power BI will show every record that matches with every type.
My advise would be to create a identifier, one last question, are the PO and ID similar or should be?
BR,
DR
Hi @Anonymous, I´ll post here the tables and share a .pbix when I get home if you need it.
PO and ID are completely different columns.
You are right, when you merge the tables you get every record that matches with every type (4th picture in the OP). I think I need a function to replace the values marked red as nulls.
P1 | A |
P2 | A |
P3 | B |
P4 | B |
L1 | A |
L2 | A |
L3 | A |
L4 | A |
L5 | B |
For what I see you need to create an identifier and merge with the merge type you want.
I would do this on the firts table:
Column1 Column2 Concat
P1 | A | P1-A |
P2 | A | P2-A |
P3 | B | P3-B |
P4 | B | P4-B |
And this to the second one:
Column1 Column2 Column1 - Copy Concat
L1 | A | P1 | P1-A |
L2 | A | P2 | P2-A |
L3 | A | P3 | P3-A |
L4 | A | P4 | P4-A |
L5 | B | P5 | P5-B |
Here you can merge all the ways you can possibly want by the Concat columns.
Let me know if it helped.
BR,
DR
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
63 | |
57 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |