Joining Multiple Tables Together and zero out values

T

tqn626

New Member
Joined
Jul 1, 2013
Messages
49
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

[TABLE="width: 367"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Transaction Number[/TD]
[TD]Transaction Line[/TD]
[TD]Gross Sales[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1563.12[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1302.6[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8676.36[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7850.04[/TD]
[/TR]
</tbody>[/TABLE]

Discount Table 1

[TABLE="width: 509"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice Number[/TD]
[TD]Invoice Line Number[/TD]
[TD]Promotion Code[/TD]
[TD]Discount Value[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]1[/TD]
[TD]SCB1.00 [/TD]
[TD="align: right"]15.63[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]2[/TD]
[TD]SCB1.00 [/TD]
[TD="align: right"]13.03[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]3[/TD]
[TD]SCRM1.00 [/TD]
[TD="align: right"]86.76[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]1[/TD]
[TD]PDS[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]4[/TD]
[TD]SCRM1.00 [/TD]
[TD="align: right"]78.5[/TD]
[/TR]
</tbody>[/TABLE]


Discount Table 2

[TABLE="width: 549"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice Number[/TD]
[TD]Invoice Line Number[/TD]
[TD]Adjustment Code[/TD]
[TD]Total Misc Adj Value[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]1[/TD]
[TD].20COFFEE [/TD]
[TD="align: right"]-15.6[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]1[/TD]
[TD]CA-CRV10 [/TD]
[TD="align: right"]46.8[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]2[/TD]
[TD].20COFFEE [/TD]
[TD="align: right"]-13[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]2[/TD]
[TD]CA-CRV10 [/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]3[/TD]
[TD].17CRMRS [/TD]
[TD="align: right"]-78.54[/TD]
[/TR]
[TR]
[TD="align: right"]361449[/TD]
[TD="align: right"]4[/TD]
[TD].17CRMRS [/TD]
[TD="align: right"]-71.06[/TD]
[/TR]
</tbody>[/TABLE]

End Result

[TABLE="width: 745"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Transaction Number[/TD]
[TD]Transaction Line[/TD]
[TD]Gross Sales[/TD]
[TD]Promotion Code[/TD]
[TD]Line Level.Discount Value[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]1[/TD]
[TD]1563.12[/TD]
[TD]SCB1.00 [/TD]
[TD]15.63[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]PDS[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD].20COFFEE [/TD]
[TD]-15.6[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]CA-CRV10 [/TD]
[TD]46.8[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]2[/TD]
[TD]1302.6[/TD]
[TD]SCB1.00 [/TD]
[TD]13.03[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD].20COFFEE [/TD]
[TD]-13[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]CA-CRV10 [/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]3[/TD]
[TD]8676.36[/TD]
[TD]SCRM1.00 [/TD]
[TD]86.76[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD].17CRMRS [/TD]
[TD]-78.54[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]4[/TD]
[TD]7850.04[/TD]
[TD]SCRM1.00 [/TD]
[TD]78.5[/TD]
[/TR]
[TR]
[TD]361449[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD].17CRMRS [/TD]
[TD]-71.06[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
you can try this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Transaction Number[/td][td=bgcolor:#5B9BD5]Transaction Line[/td][td=bgcolor:#5B9BD5]Gross Sales[/td][td][/td][td][/td][td=bgcolor:#70AD47]Invoice Number[/td][td=bgcolor:#70AD47]Invoice Line Number[/td][td=bgcolor:#70AD47]Gross Sales[/td][td=bgcolor:#70AD47]Promotion Code[/td][td=bgcolor:#70AD47]Discount Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
1563.12​
[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]
361449​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
1563.12​
[/td][td=bgcolor:#E2EFDA]SCB1.00[/td][td=bgcolor:#E2EFDA]
15.63​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
2​
[/td][td]
1302.6​
[/td][td][/td][td][/td][td]
361449​
[/td][td]
1​
[/td][td]
0​
[/td][td]PDS[/td][td]
20​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
8676.36​
[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]
361449​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]CA-CRV10[/td][td=bgcolor:#E2EFDA]
46.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
4​
[/td][td]
7850.04​
[/td][td][/td][td][/td][td]
361449​
[/td][td]
1​
[/td][td]
0​
[/td][td].20COFFEE[/td][td]
-15.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]
361449​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1302.6​
[/td][td=bgcolor:#E2EFDA]SCB1.00[/td][td=bgcolor:#E2EFDA]
13.03​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]
361449​
[/td][td]
2​
[/td][td]
0​
[/td][td]CA-CRV10[/td][td]
39​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Invoice Number[/td][td=bgcolor:#5B9BD5]Invoice Line Number[/td][td=bgcolor:#5B9BD5]Promotion Code[/td][td=bgcolor:#5B9BD5]Discount Value[/td][td][/td][td=bgcolor:#E2EFDA]
361449​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA].20COFFEE[/td][td=bgcolor:#E2EFDA]
-13​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]SCB1.00[/td][td=bgcolor:#DDEBF7]
15.63​
[/td][td][/td][td]
361449​
[/td][td]
3​
[/td][td]
8676.36​
[/td][td]SCRM1.00[/td][td]
86.76​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
2​
[/td][td]SCB1.00[/td][td]
13.03​
[/td][td][/td][td=bgcolor:#E2EFDA]
361449​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA].17CRMRS[/td][td=bgcolor:#E2EFDA]
-78.54​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]SCRM1.00[/td][td=bgcolor:#DDEBF7]
86.76​
[/td][td][/td][td]
361449​
[/td][td]
4​
[/td][td]
7850.04​
[/td][td]SCRM1.00[/td][td]
78.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
1​
[/td][td]PDS[/td][td]
20​
[/td][td][/td][td=bgcolor:#E2EFDA]
361449​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA].17CRMRS[/td][td=bgcolor:#E2EFDA]
-71.06​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]SCRM1.00[/td][td=bgcolor:#DDEBF7]
78.5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Invoice Number[/td][td=bgcolor:#5B9BD5]Invoice Line Number[/td][td=bgcolor:#5B9BD5]Adjustment Code[/td][td=bgcolor:#5B9BD5]Total Misc Adj Value[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7].20COFFEE[/td][td=bgcolor:#DDEBF7]
-15.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
1​
[/td][td]CA-CRV10[/td][td]
46.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7].20COFFEE[/td][td=bgcolor:#DDEBF7]
-13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
2​
[/td][td]CA-CRV10[/td][td]
39​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
361449​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7].17CRMRS[/td][td=bgcolor:#DDEBF7]
-78.54​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
361449​
[/td][td]
4​
[/td][td].17CRMRS[/td][td]
-71.06​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Example Excel File
 
Upvote 0
Woah, what black magic is this. I will try it out with my much larger dataset! Thank you so much.
 
Upvote 0
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
 
Last edited:
Upvote 0
try

Code: 
[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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Power Query to split a column containing varying numbers of items and organize results under matching columns
Replies
6
Views
698
shaowu459
shaowu459
J
  • Question Question
Created Macro That Formats Sales Data through power query and keep getting errors when I try and run it
Replies
1
Views
707
Fluff
Fluff
nikio8
  • Question Question
power query, vlookup (left join) unwanted sort
Replies
6
Views
911
sandy666
sandy666
P
  • Question Question
form from/to areas of single lines works very slow
2 3
Replies
21
Views
4K
sandy666
sandy666
R
  • Question Question
"Page" format to table - Power Pivot or Query
Replies
1
Views
801
RooBoyAust
R

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top