Cannot Combine queries in power query

%3CLINGO-SUB%20id%3D%22lingo-sub-1512492%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20Combine%20queries%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512492%22%20slang%3D%22en-US%22%3E(Using%20excel%202016)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1512441%22%20slang%3D%22en-US%22%3ECannot%20Combine%20queries%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512441%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20created%20a%20power%20query%20(fetching%20and%20parsing%20a%20JSON)%3C%2FP%3E%3CP%3E-%26gt%3B%20It%20fetches%20a%20subset%20of%20records%20form%20a%20website.%3C%2FP%3E%3CP%3EI%20have%20a%20worksheet%20with%20all%20possible%20records%20that%20could%20be%20coming%20in.%20I've%20made%20it%20a%20table%20and%20imported%20it%20into%20power%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20i%20try%20to%20combine%20the%20two%20sources%3A%20the%20merge%20window%20opens%2C%20i%20can%20select%20both%20queries%2C%20but%20then%20i%20cannot%20press%20'ok'%3B%20it's%20grayed%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorkaround%201%3A%3C%2FP%3E%3CP%3EAlso%20making%20a%20connection%20between%20the%202%20in%20relations%20manager%20makes%20a%201-to-many%20connection%2C%20even%20though%20BOTH%20recordsets%20are%20unique%20rows%20on%20the%20field%20i%20want%20to%20match%2C%20and%20i've%20made%20both%20the%20same%20name%20too.%20%3D%26gt%3B%20workaround%20using%20'related'%20won'r%20work%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorkarround%202%3A%3C%2FP%3E%3CP%3EMy%20next%20workaround%20was%20to%20do%20the%20final%20combines%20via%20a%20lookup%20statement%20in%20power%20pivot.%3C%2FP%3E%3CP%3EAfter%20some%20headaches%2C%20i%20managed%20to%20get%20that%20working%2C%20but%20the%20Calculated%20columns%20will%20not%20show%20up%20in%20the%20original%20power%20query%20output%20(on%20the%20sheet)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20i%20do%20that%20last%20bit%3F%3CBR%20%2F%3E(or%20get%20the%20merge%20to%20work%20to%20do%20it%20in%20powerquery%2C%20my%20preferred%20choice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20data%3A%3C%2FP%3E%3CP%3EFull%20set%3A%3C%2FP%3E%3CP%3Eaaa%20ccc%20askmefff%3C%2FP%3E%3CP%3E111%20333%20askmefff%3CBR%20%2F%3E222%20444%20askemefff%3C%2FP%3E%3CP%3E444%20666%20askSomeoneelse%3C%2FP%3E%3CP%3E333%20666%20askSomeoneelse%3C%2FP%3E%3CP%3E666%20999%20askSomeoneelse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPartial%20set%3C%2FP%3E%3CP%3E1%202%203%204%205%20askemefff%3C%2FP%3E%3CP%3E4%203%202%201%20askmeff%3C%2FP%3E%3CP%3E8%26nbsp%3B%202%206%202%20askSomeoneelse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20Found%20out%20i%20missed%20a%20step%3B%20it's%20not%20very%20obvious%20you%20have%20to%20click%20on%20columns%20in%20both%20to%20'connect'%20them.%20Done%20and%20it%20does%20work.%3B)%20(when%20you%20see%20the%20'previews%20of%20both%20tables%2C%20click%20on%20the%20column%20that%3B's%20the%20same%20for%20both).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512441%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

Hey all.

 

I've created a power query (fetching and parsing a JSON)

-> It fetches a subset of records form a website.

I have a worksheet with all possible records that could be coming in. I've made it a table and imported it into power query.

 

But when i try to combine the two sources: the merge window opens, i can select both queries, but then i cannot press 'ok'; it's grayed out.

 

Workaround 1:

Also making a connection between the 2 in relations manager makes a 1-to-many connection, even though BOTH recordsets are unique rows on the field i want to match, and i've made both the same name too. => workaround using 'related' won'r work

 

Workarround 2:

My next workaround was to do the final combines via a lookup statement in power pivot.

After some headaches, i managed to get that working, but the Calculated columns will not show up in the original power query output (on the sheet)

 

How can i do that last bit?
(or get the merge to work to do it in powerquery, my preferred choice.

 

Thanks!

 

 

Example data:

Full set:

aaa ccc askmefff

111 333 askmefff
222 444 askemefff

444 666 askSomeoneelse

333 666 askSomeoneelse

666 999 askSomeoneelse

 

Partial set

1 2 3 4 5 askemefff

4 3 2 1 askmeff

8  2 6 2 askSomeoneelse

 

 

EDIT: Found out i missed a step; it's not very obvious you have to click on columns in both to 'connect' them. Done and it does work.;) (when you see the 'previews of both tables, click on the column that;'s the same for both).

1 Reply
Highlighted
(Using excel 2016)