Inner Join question

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Inner Join question

Postby PhilC » Wed May 17, 2017 1:42 pm

Hi,

Am looking for a solution that would allow me to use a manually maintained table as a filter for a dataset, but also allow no filtering to occur.

I set up a one column table with values for the Budget Centres I wanted to be included in the data loaded, and used an Inner Join. This works if I only want a filtered dataset, however if I do not want to filter at all (ie no rows in Budget Centre table), this approach results in no values due to the inner join.

Am wondering what options for a solution I might have?

1. One approach might be to create a table from the Budget Centre field in the dataset, link that to the Budget Centre filter table using an outer join so all elements remain, then work out how to filter that list based on whether there are any values from the filter table

2. Is there a way to only perform a step if a condition is met? ie Only Merge Tables if Budget Centre table has 1 or more rows.

Suggestions welcome.

Cheers
Phil
Attachments
PPF Inner Join Question.xlsx
(138.91 KiB) Downloaded 8 times

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Inner Join question

Postby PhilC » Wed May 17, 2017 3:25 pm

Came across the following post that might be useful but cannot get it to work.

https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then-else-gt/ba-p/39998

M code is

Code: Select all

let
    Source = Data,
   

    #"Merged Queries" = Table.NestedJoin(Source,{"Budget Centre"},BudgetCentre,{"Budget Centre"},"NewColumn",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"NewColumn"}),

    result =
        if Parameters("Budget Centre Count") = "0"
        then Source
        else #"Removed Columns"

in
    result


But returns an error/: An error occurred in the ‘Parameters’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.

Cheers
Phil
Attachments
PPF Inner Join Question v2.xlsx
(140.45 KiB) Downloaded 7 times


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest