convert Two tables into one table

[tangutoori]

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

[v-lili6-msft · Accepted solution]

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

[PattemManohar]

@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"