Powerpivot joins.. not seem to be working

jak82

Board Regular
Joined
Apr 28, 2016
Messages
146
Hi,

I have done a basic file.. see


Whats wierd is the joins dont seem to be working.. anyone got any idea why.. scratching my head.

Chris
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,037
Office Version
2019
Platform
Windows
VBA Code:
let
    Source = Table.NestedJoin(Table1, {"Qid"}, Table2, {"Qid"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Doc"}, {"Table2.Doc"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table2",{"Qid", "Table2.Doc", "Text"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Qid", Order.Ascending}, {"Table2.Doc", Order.Ascending}})
in
    #"Sorted Rows"
 

jak82

Board Regular
Joined
Apr 28, 2016
Messages
146
VBA Code:
let
    Source = Table.NestedJoin(Table1, {"Qid"}, Table2, {"Qid"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Doc"}, {"Table2.Doc"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table2",{"Qid", "Table2.Doc", "Text"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Qid", Order.Ascending}, {"Table2.Doc", Order.Ascending}})
in
    #"Sorted Rows"
Hi @alansidman

I am trying to do it with the joins in powerpivot without having to code things..... as its for junior people....the basics dont seem to be working in Excel?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,259
I don't understand your steps: load tables to Power Query next to Data Model next create Relationship next Pivot Table
if you want to create relationship enough load tables to Data Model next Pivot Table without Power Query
or without Data Model just Power Query only, merge tables and Pivot Table from External Data Source

Examples with Power Query only and with Data Model only: Example
 

jak82

Board Regular
Joined
Apr 28, 2016
Messages
146
Thanks @sandy666 Ill have a look at that.. for some reason though the powerpivot join wasnt working on mine.. not sure.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,259
You are welcome

If you want result like on the picture I posted before you are right, PowerPivot doesn't work like you want
 

Forum statistics

Threads
1,078,008
Messages
5,337,706
Members
399,162
Latest member
Sabina

Some videos you may like

This Week's Hot Topics

Top