Joining Multiple Tables Together and zero out values

[tqn626]

I've been googling for a while and can not find the solution to this.

I'm trying to Join a Sales Table with two separate discount tables. When I join the tables together the Sales is repeated on each line. I want to have the sales number show up only once and have the rest of the rows show zero. I also want the two discount columns to be in the same column.

If anyone can point me in the right direction I would eternally grateful.

Sale Data

Transaction Number	Transaction Line	Gross Sales
361449	1	1563.12
361449	2	1302.6
361449	3	8676.36
361449	4	7850.04

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Discount Table 1

Invoice Number	Invoice Line Number	Promotion Code	Discount Value
361449	1	SCB1.00	15.63
361449	2	SCB1.00	13.03
361449	3	SCRM1.00	86.76
361449	1	PDS	20
361449	4	SCRM1.00	78.5

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Discount Table 2

Invoice Number	Invoice Line Number	Adjustment Code	Total Misc Adj Value
361449	1	.20COFFEE	-15.6
361449	1	CA-CRV10	46.8
361449	2	.20COFFEE	-13
361449	2	CA-CRV10	39
361449	3	.17CRMRS	-78.54
361449	4	.17CRMRS	-71.06

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

End Result

Transaction Number	Transaction Line	Gross Sales	Promotion Code	Line Level.Discount Value
361449	1	1563.12	SCB1.00	15.63
361449	1	0	PDS	20
361449	1	0	.20COFFEE	-15.6
361449	1	0	CA-CRV10	46.8
361449	2	1302.6	SCB1.00	13.03
361449	2	0	.20COFFEE	-13
361449	2	0	CA-CRV10	39
361449	3	8676.36	SCRM1.00	86.76
361449	3	0	.17CRMRS	-78.54
361449	4	7850.04	SCRM1.00	78.5
361449	4	0	.17CRMRS	-71.06

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

you can try this:

Transaction Number	Transaction Line	Gross Sales			Invoice Number	Invoice Line Number	Gross Sales	Promotion Code	Discount Value
361449​	1​	1563.12​			361449​	1​	1563.12​	SCB1.00	15.63​
361449​	2​	1302.6​			361449​	1​	0​	PDS	20​
361449​	3​	8676.36​			361449​	1​	0​	CA-CRV10	46.8​
361449​	4​	7850.04​			361449​	1​	0​	.20COFFEE	-15.6​
					361449​	2​	1302.6​	SCB1.00	13.03​
					361449​	2​	0​	CA-CRV10	39​
Invoice Number	Invoice Line Number	Promotion Code	Discount Value		361449​	2​	0​	.20COFFEE	-13​
361449​	1​	SCB1.00	15.63​		361449​	3​	8676.36​	SCRM1.00	86.76​
361449​	2​	SCB1.00	13.03​		361449​	3​	0​	.17CRMRS	-78.54​
361449​	3​	SCRM1.00	86.76​		361449​	4​	7850.04​	SCRM1.00	78.5​
361449​	1​	PDS	20​		361449​	4​	0​	.17CRMRS	-71.06​
361449​	4​	SCRM1.00	78.5​
Invoice Number	Invoice Line Number	Adjustment Code	Total Misc Adj Value
361449​	1​	.20COFFEE	-15.6​
361449​	1​	CA-CRV10	46.8​
361449​	2​	.20COFFEE	-13​
361449​	2​	CA-CRV10	39​
361449​	3​	.17CRMRS	-78.54​
361449​	4​	.17CRMRS	-71.06​

Example Excel File

 

[tqn626]

Woah, what black magic is this. I will try it out with my much larger dataset! Thank you so much.

 

[sandy666]

Just magic, especially black

You are welcome

Have a nice day

 

[tqn626]

Is there any way to write this in one Power Query and not have multiple merge and appends?

 

[sandy666]

What is wrong with appends and merges? It's more clear...
but it's possible to do that what you want
anyway if you have more tables from different tasks in Workbook Queries simply Group tables for each task

 

[sandy666]

try

[SIZE=1]let
    Source1 = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Discount1"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="Discount2"]}[Content],
    RenCols = Table.RenameColumns(Source3,{{"Adjustment Code", "Promotion Code"}, {"Total Misc Adj Value", "Discount Value"}}),
    Append = Table.Combine({Discount1, RenCols}),
    Merge = Table.NestedJoin(Sales,{"Transaction Line"},Append,{"Invoice Line Number"},"mrg",JoinKind.Inner),
    ROCs = Table.SelectColumns(Merge,{"Gross Sales", "mrg"}),
    Expand = Table.ExpandTableColumn(ROCs, "mrg", {"Invoice Number", "Invoice Line Number", "Promotion Code", "Discount Value"}, {"Invoice Number", "Invoice Line Number", "Promotion Code", "Discount Value"}),
    Reorder = Table.ReorderColumns(Expand,{"Invoice Number", "Invoice Line Number", "Gross Sales", "Promotion Code", "Discount Value"}),
    Sorted = Table.Sort(Reorder,{{"Invoice Line Number", Order.Ascending}, {"Promotion Code", Order.Descending}}),
    Duplicate = Table.Sort(Reorder,{{"Invoice Line Number", Order.Ascending}, {"Promotion Code", Order.Descending}}),
    RemDup = Table.Distinct(Duplicate, {"Gross Sales"}),
    Distinct = Table.SelectColumns(RemDup,{"Gross Sales", "Promotion Code"}),
    Merged = Table.NestedJoin(Distinct,{"Gross Sales", "Promotion Code"},Sorted,{"Gross Sales", "Promotion Code"},"Merge",JoinKind.RightOuter),
    RC = Table.RemoveColumns(Merged,{"Promotion Code"}),
    ExpMerge = Table.ExpandTableColumn(RC, "Merge", {"Invoice Number", "Invoice Line Number", "Promotion Code", "Discount Value"}, {"Invoice Number", "Invoice Line Number", "Promotion Code", "Discount Value"}),
    Reorder2 = Table.ReorderColumns(ExpMerge,{"Invoice Number", "Invoice Line Number", "Gross Sales", "Promotion Code", "Discount Value"}),
    Replace = Table.ReplaceValue(Reorder2,null,0,Replacer.ReplaceValue,{"Gross Sales"}),
    LastSort = Table.Sort(Replace,{{"Invoice Line Number", Order.Ascending}, {"Promotion Code", Order.Descending}})
in
    LastSort[/SIZE]

define names for each table, Sales, Discount1, Discount2 in Name Manager
then Data - New Query - From Other Sources - Blank Query
in Advanced Editor replace code with copied from the post