Apply a custom Query to a salesforce data object import

[djerryanderson]

I'm new to Power BI and DAX. Been using SSRS and Visual Studio for a few years so I'm used to how that works. I've figured out how to connect and import data from Salesforce (in my case the Contacts table). I go to the DAX query view and write a custom query that creates the table I want. I cannot figure out to apply that output to report!

How do I get this query (which works perfectly):

To this:

BTW I have tried the Advanced Editor in Transform Data and tried "meshing" my query to the data import but although it says the syntax is correct I get errors:

I'm sure I'm going about this all wrong. I must be bad at finding tutorials that cover this as well because I can't find any step by step walkthroughs on it. I can probably figure it out if I just new "where" to link the report to the table that the custom query creates. Right now it just shows the entire contacts table. 

[djerryanderson]

This was just an issue with using the dev studio correctly. Using @Fowmy's suggestion I figured out how to add the table to the report. That's all I really needed to do. I've used SSRS for a few years and just couldn't figure out (in Power BI) how to add the table to the report so I could actually "use" it.

[Fowmy]

@djerryanderson 

Powre Query is case sensitive "IF"  should be "if"

[djerryanderson]

Well this clears it up a bit - power query v DAX query. SO I need to learn different syntax. The case sentive nature of the "if" apparently is not all I need to do - ISBLANK apparently is not correct either.

[Anonymous]

Hi @djerryanderson ,

Here I create a sample and I think you can refer to it.

As far as I know, we could only add one column in one time by Table.AddColumn Function. 

Here I suggest you to add columns you need by if... then... else and then use Table.SelectColumns Function.

let
    Source = ...,
    #"Query" = ...,
    #"Added Street" = Table.AddColumn(Query, "Street", each if [MailingCity] = null then [Home_Address_Street_s] else [MailingStreet]),
    #"Added City" = Table.AddColumn(#"Added Street", "City", each if [MailingCity] = null then [Home_Address_City_s] else [MailingCity]),
    #"SelectColumns" = Table.SelectColumns(#"Added City",{"FirstName","LastName","Chirstmas_Gift_C","Street","City"})
in
    #"SelectColumns"

Result of my sample is as below.

The logic of "State" and "Zip" columns are the same as "Street" and ”City“.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy · Accepted solution]

@djerryanderson 

I did not look into your code, just saw the IF and replied 🙂

The DAX code you wrote could be converted in to a table New Table under Modeling tab