cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tangutoori Frequent Visitor
Frequent Visitor

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 
CAMPAIGNSTAGEQTY(7 MAR) 
First Time Right Campaign - Telstra CoEInnovation Champion Review (L1)1 
First Time Right Campaign - Telstra CoERejected1 
Great Ideas for My BU - CommsClarification3 
Great Ideas for My BU - CommsDeleted1 
Great Ideas for My BU - CommsInnovation Champion Review (L1)1 
Great Ideas for My BU - CommsRejected3 
Great Ideas for My BU - I&ENRDeleted2 
Great Ideas for My BU - I&ENRInnovation Champion Review (L1)2 
Great Ideas for My BU - I&ENRRejected2 
Great Ideas For My BU - M&HRejected1 
Great Ideas For My BU -TransportationRejected1 
My Ideas - CommunicationsOn-hold16 
My Ideas - CommunicationsReporting Manager Review1 
My Ideas - DLMApproved14 
My Ideas - DLMClarification3 
My Ideas - DLMCoE head / SME review1 
My Ideas - DLMImplemented18 
My Ideas - DLMOn-hold5 
My Ideas - DLMProject3 
My Ideas - DLMRejected12 
    
TABLE 2 
CAMPAIGNSTAGEQTY(8 MAR) 
My Ideas - CommunicationsOn-hold18 
My Ideas - CommunicationsReporting Manager Review2 
My Ideas - DLMApproved14 
My Ideas - DLMClarification3 
My Ideas - DLMCoE head / SME review1 
My Ideas - DLMImplemented18 
My Ideas - DLMOn-hold5 
My Ideas - DLMProject3 
My Ideas - DLMRejected12 
Productivity Improvement  Ideas - ALSTOMClarification1 
    
EXPECTED OUTPUT 
    
CAMPAIGNSTAGEQTY(7 MAR)QTY(8 MAR)
First Time Right Campaign - Telstra CoEInnovation Champion Review (L1)1null
First Time Right Campaign - Telstra CoERejected1null
Great Ideas for My BU - CommsClarification3null
Great Ideas for My BU - CommsDeleted1null
Great Ideas for My BU - CommsInnovation Champion Review (L1)1null
Great Ideas for My BU - CommsRejected3null
Great Ideas for My BU - I&ENRDeleted2null
Great Ideas for My BU - I&ENRInnovation Champion Review (L1)2null
Great Ideas for My BU - I&ENRRejected2null
Great Ideas For My BU - M&HRejected1null
Great Ideas For My BU -TransportationRejected1null
My Ideas - CommunicationsOn-hold1618
My Ideas - CommunicationsReporting Manager Review12
My Ideas - DLMApproved1414
My Ideas - DLMClarification33
My Ideas - DLMCoE head / SME review11
My Ideas - DLMImplemented1818
My Ideas - DLMOn-hold55
My Ideas - DLMProject33
My Ideas - DLMRejected1212
Productivity Improvement  Ideas - ALSTOMClarificationnull1
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: convert Two tables into one table

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:

1.JPG

 

and here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User
Super User

Re: convert Two tables into one table

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

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Community Support Team
Community Support Team

Re: convert Two tables into one table

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:

1.JPG

 

and here is pbix file, please try it.

 

Best Regards,

Lin

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