Powerpivot joins.. not seem to be working

[jak82]

Hi,

I have done a basic file.. see

testexample.xlsx

Shared with Dropbox

www.dropbox.com

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

Chris

 

[sandy666]

is that what you want?

 

[alansidman]

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]

is that what you want?

Yes...though for some reason the joins do not seem to be working...

 

[jak82]

@sandy666 Yes...though for some reason the joins do not seem to be working...

 

[jak82]

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]

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]

Thanks @sandy666 Ill have a look at that.. for some reason though the powerpivot join wasnt working on mine.. not sure.

 

[sandy666]

You are welcome

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