Merge multiple queries with same header without creating duplicate columns

[Anonymous]

I am having trouble merging queries that have partial data. I am trying to merge Queries B and C with Query A. The end result will create 2 columns for "Sales". I would like "Sales" to be in the same column. Also, I know appending B and C would solve for the sample data, but this will not work for my actual data so let's assume appending is not an option. Here are my sample Queries:

Query A

Name	Week Number
Jon	1
Jack	1
Hank	1
Jill	1
Jon	2
Jack	2
Hank	2
Jill	2

Query B

Name	Week Number	Sales
Jon	1	50
Jack	1	30
Hank	1	60
Jill	1	70

Query C

Name	Week Number	Sales
Jon	2	75
Jack	2	35
Hank	2	65
Jill	2	45

Actual Result

Name	Week Number	Sales	Sales 1
Jon	1	50
Jack	1	30
Hank	1	60
Jill	1	70
Jon	2		75
Jack	2		35
Hank	2		65
Jill	2		45

Desired Result

Name	Week Number	Sales
Jon	1	50
Jack	1	30
Hank	1	60
Jill	1	70
Jon	2	75
Jack	2	35
Hank	2	65
Jill	2	45

Thank you in advance for your help!

[Mariusz]

Hi @Anonymous 

You can append Query B and C and later Merge appended result into Qury A.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Thanks for the response. Appending isn't an option for my actual dataset. Essentially I am asking if there is a way to merge queries so the values in the expanded columns can be populated in an existing column rather than creating a new column.

[Mariusz · Accepted solution]

Hi @Anonymous 

I'm sure you could write M function to do it, but the easiest solution would be merging all your tables, expanding and later merging the columns together as below.
Why append is not an option?

let
    Source = Table.NestedJoin(#"Query A", {"Name", "Week Number"}, #"Query B", {"Name", "Week Number"}, "Query B", JoinKind.LeftOuter),
    #"Expanded Query B" = Table.ExpandTableColumn(Source, "Query B", {"Sales"}, {"Query B.Sales"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Query B", {"Name", "Week Number"}, #"Query C", {"Name", "Week Number"}, "Query C", JoinKind.LeftOuter),
    #"Expanded Query C" = Table.ExpandTableColumn(#"Merged Queries", "Query C", {"Sales"}, {"Query C.Sales"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Query C", {"Name", "Week Number"}, #"Query D", {"Name", "Week Number"}, "Query D", JoinKind.LeftOuter),
    #"Expanded Query D" = Table.ExpandTableColumn(#"Merged Queries1", "Query D", {"Sales"}, {"Query D.Sales"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Query D", {{"Query B.Sales", type text}, {"Query C.Sales", type text}, {"Query D.Sales", type text}}, "en-GB"),{"Query B.Sales", "Query C.Sales", "Query D.Sales"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.