"Append to the right" not below

[Anonymous]

Hi, I´m trying to do this,

Given a table with orders:

Orders

And another one with the stock to satisfy those orders:

Stock

I´d like to paste them together by [Type] like this:

Result

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.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create "rank" and "merged" column in "Orders" and "Stock" table,

Then merge two tables based on "Merged1"and "Merged2" columns,

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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"

 

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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