Forum Discussion
Solved
convert Two tables into one table
Dear tems,
there are two tables.i want all the records of two tables into single table.
below is the sample data.
| TABLE 1 | |||
| CAMPAIGN | STAGE | QTY(7 MAR) | |
| First Time Right Campaign - Telstra CoE | Innovation Champion Review (L1) | 1 | |
| First Time Right Campaign - Telstra CoE | Rejected | 1 | |
| Great Ideas for My BU - Comms | Clarification | 3 | |
| Great Ideas for My BU - Comms | Deleted | 1 | |
| Great Ideas for My BU - Comms | Innovation Champion Review (L1) | 1 | |
| Great Ideas for My BU - Comms | Rejected | 3 | |
| Great Ideas for My BU - I&ENR | Deleted | 2 | |
| Great Ideas for My BU - I&ENR | Innovation Champion Review (L1) | 2 | |
| Great Ideas for My BU - I&ENR | Rejected | 2 | |
| Great Ideas For My BU - M&H | Rejected | 1 | |
| Great Ideas For My BU -Transportation | Rejected | 1 | |
| My Ideas - Communications | On-hold | 16 | |
| My Ideas - Communications | Reporting Manager Review | 1 | |
| My Ideas - DLM | Approved | 14 | |
| My Ideas - DLM | Clarification | 3 | |
| My Ideas - DLM | CoE head / SME review | 1 | |
| My Ideas - DLM | Implemented | 18 | |
| My Ideas - DLM | On-hold | 5 | |
| My Ideas - DLM | Project | 3 | |
| My Ideas - DLM | Rejected | 12 | |
| TABLE 2 | |||
| CAMPAIGN | STAGE | QTY(8 MAR) | |
| My Ideas - Communications | On-hold | 18 | |
| My Ideas - Communications | Reporting Manager Review | 2 | |
| My Ideas - DLM | Approved | 14 | |
| My Ideas - DLM | Clarification | 3 | |
| My Ideas - DLM | CoE head / SME review | 1 | |
| My Ideas - DLM | Implemented | 18 | |
| My Ideas - DLM | On-hold | 5 | |
| My Ideas - DLM | Project | 3 | |
| My Ideas - DLM | Rejected | 12 | |
| Productivity Improvement Ideas - ALSTOM | Clarification | 1 | |
| EXPECTED OUTPUT | |||
| CAMPAIGN | STAGE | QTY(7 MAR) | QTY(8 MAR) |
| First Time Right Campaign - Telstra CoE | Innovation Champion Review (L1) | 1 | null |
| First Time Right Campaign - Telstra CoE | Rejected | 1 | null |
| Great Ideas for My BU - Comms | Clarification | 3 | null |
| Great Ideas for My BU - Comms | Deleted | 1 | null |
| Great Ideas for My BU - Comms | Innovation Champion Review (L1) | 1 | null |
| Great Ideas for My BU - Comms | Rejected | 3 | null |
| Great Ideas for My BU - I&ENR | Deleted | 2 | null |
| Great Ideas for My BU - I&ENR | Innovation Champion Review (L1) | 2 | null |
| Great Ideas for My BU - I&ENR | Rejected | 2 | null |
| Great Ideas For My BU - M&H | Rejected | 1 | null |
| Great Ideas For My BU -Transportation | Rejected | 1 | null |
| My Ideas - Communications | On-hold | 16 | 18 |
| My Ideas - Communications | Reporting Manager Review | 1 | 2 |
| My Ideas - DLM | Approved | 14 | 14 |
| My Ideas - DLM | Clarification | 3 | 3 |
| My Ideas - DLM | CoE head / SME review | 1 | 1 |
| My Ideas - DLM | Implemented | 18 | 18 |
| My Ideas - DLM | On-hold | 5 | 5 |
| My Ideas - DLM | Project | 3 | 3 |
| My Ideas - DLM | Rejected | 12 | 12 |
| Productivity Improvement Ideas - ALSTOM | Clarification | null | 1 |
hi, tangutoori
You could try this way in Edit Queries:
let Source = Table.FuzzyNestedJoin(Table1,{"Campaign", "Stage"},Table2,{"Campaign", "Stage"},"Test250Join2",JoinKind.FullOuter,[IgnoreCase=true, IgnoreSpace=true]), #"Expanded Test250Join2" = Table.ExpandTableColumn(Source, "Test250Join2", {"Campaign", "Stage", "QTY(8 MAR)"}, {"Campaign.1", "Stage.1", "QTY(8 MAR)"}), #"Added Custom" = Table.AddColumn(#"Expanded Test250Join2", "Custom", each if [Campaign]=null then [Campaign.1] else[Campaign]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Stage]=null then [Stage.1] else[Stage]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Campaign", "Stage", "Campaign.1", "Stage.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Qty(7Mar)", "QTY(8 MAR)"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Campaign"}, {"Custom.1", "Stage"}}) in #"Renamed Columns"Result:
and here is pbix file, please try it.
Best Regards,
Lin
2 Replies
tangutoori Please try using "Merge Queries" option in "Power Query Editor"
Here is the steps generated to achieve the expected output.
let Source = Table.NestedJoin(Test250Join1,{"Campaign"},Test250Join2,{"Campaign"},"Test250Join2",JoinKind.FullOuter), #"Expanded Test250Join2" = Table.ExpandTableColumn(Source, "Test250Join2", {"Campaign", "Stage", "Qty(8Mar)"}, {"Test250Join2.Campaign", "Test250Join2.Stage", "Test250Join2.Qty(8Mar)"}), #"Added Custom" = Table.AddColumn(#"Expanded Test250Join2", "CampaignNew", each if Text.Length([Campaign])>0 then [Campaign] else [Test250Join2.Campaign]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CampaignNew"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "CampaingNew", each if [Campaign] = null then [Test250Join2.Campaign] else [Campaign]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "StageNew", each if [Stage] = null then [Test250Join2.Stage] else [Stage]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Campaign", "Stage"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Test250Join2.Campaign", "Test250Join2.Stage", "Test250Join2.Qty(8Mar)", "CampaingNew", "StageNew", "Qty(7Mar)"}), #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Test250Join2.Campaign", "Test250Join2.Stage"}), #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"CampaingNew", "StageNew", "Qty(7Mar)", "Test250Join2.Qty(8Mar)"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"CampaingNew", "Campaingn"}, {"StageNew", "Stage"}, {"Test250Join2.Qty(8Mar)", "Qty(8Mar)"}}) in #"Renamed Columns"hi, tangutoori
You could try this way in Edit Queries:
let Source = Table.FuzzyNestedJoin(Table1,{"Campaign", "Stage"},Table2,{"Campaign", "Stage"},"Test250Join2",JoinKind.FullOuter,[IgnoreCase=true, IgnoreSpace=true]), #"Expanded Test250Join2" = Table.ExpandTableColumn(Source, "Test250Join2", {"Campaign", "Stage", "QTY(8 MAR)"}, {"Campaign.1", "Stage.1", "QTY(8 MAR)"}), #"Added Custom" = Table.AddColumn(#"Expanded Test250Join2", "Custom", each if [Campaign]=null then [Campaign.1] else[Campaign]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Stage]=null then [Stage.1] else[Stage]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Campaign", "Stage", "Campaign.1", "Stage.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Qty(7Mar)", "QTY(8 MAR)"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Campaign"}, {"Custom.1", "Stage"}}) in #"Renamed Columns"Result:
and here is pbix file, please try it.
Best Regards,
Lin
