Power Query Conditional Data Source

[Kimcha]

Hi,

I currently have two ways to analyse our CRM data: A mysql database of our data or a CSV export.

We are testing a provider that is syncing the DB to mysql, but are not sure if we will continue with him. I would like to design my power query queries in a way that makes it easy to switch between CSV and mysql.

Ideally, I would like to define a parameter 'use_dw' and if it's true, pull from the MYSQL DB, and if it's false from CSV.

My query looks like this right now:

let
//    Source = Csv.Document(File.Contents(data_path & "\is_sync\is_sync_latest\Contact.csv"),[Delimiter=",", Columns=80, Encoding=1252, QuoteStyle=QuoteStyle.None]),
//    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
//    #"Table Ready" = #"Promoted Headers",

// Data Warehouse Sync
    Source = MySQL.Database(dw_host, dw_db, [ReturnSingleDatabase=true]),
    xxx_infusionsoft_table = Source{[Schema="xxx",Item="infusionsoft_contacts"]}[Data],
    #"Removed DW Columns" = Table.RemoveColumns(xxx_infusionsoft_table,{"id", "last_synced", "needs_resync", "created", "modified"}),
    #"Fixed Header" = Table.TransformColumnNames(#"Removed DW Columns", (columnName as text) as text => Text.Replace(columnName, "inf_", "")),
//    #"Added LastUpdated" = Table.AddColumn(#"Fixed Header", "LastUpdated", each null),
//    #"Renamed infusionsoftid" = Table.RenameColumns(#"Added LastUpdated",{{"infusionsoftid", "Id"}}),
    #"Renamed infusionsoftid" = Table.RenameColumns(#"Fixed Header",{{"infusionsoftid", "Id"}}),
    #"Table Ready" = #"Renamed infusionsoftid",

// Transform
    #"Changed Type" = Table.TransformColumnTypes(#"Table Ready",{{"Id", Int64.Type}, {"AccountId", Int64.Type}, {"Address1Type", type text}, {"Address2Street1", type text}, {"Address2Street2", type text}, {"Address2Type", type text}, {"Address3Street1", type text}, {"Address3Street2", type text}, {"Address3Type", type text}, {"Anniversary", type text}, {"AssistantName", type text}, {"AssistantPhone", type text}, {"BillingInformation", type text}, {"Birthday", type text}, {"City", type text}, {"City2", type text}, {"City3", type text}, {"Company", type text}, {"CompanyID", Int64.Type}, {"ContactNotes", type text}, {"ContactType", type text}, {"Country", type text}, {"Country2", type text}, {"Country3", type text}, {"CreatedBy", Int64.Type}, {"DateCreated", type datetime}, {"Email", type text}, {"EmailAddress2", type text}, {"EmailAddress3", type text}, {"Fax1", type text}, {"Fax1Type", type text}, {"Fax2", type text}, {"Fax2Type", type text}, {"FirstName", type text}, {"Groups", type number}, {"JobTitle", type text}, {"Language", type text}, {"LastName", type text}, {"LastUpdated", type datetime}, {"LastUpdatedBy", Int64.Type}, {"LeadSourceId", Int64.Type}, {"Leadsource", type text}, {"MiddleName", type text}, {"Nickname", type text}, {"OwnerID", Int64.Type}, {"Password", type text}, {"Phone1", type text}, {"Phone1Ext", type text}, {"Phone1Type", type text}, {"Phone2", type text}, {"Phone2Ext", type text}, {"Phone2Type", type text}, {"Phone3", type text}, {"Phone3Ext", type text}, {"Phone3Type", type text}, {"Phone4", type text}, {"Phone4Ext", type text}, {"Phone4Type", type text}, {"Phone5", type text}, {"Phone5Ext", type text}, {"Phone5Type", type text}, {"PostalCode", type text}, {"PostalCode2", type text}, {"PostalCode3", type text}, {"ReferralCode", type text}, {"SpouseName", type text}, {"State", type text}, {"State2", type text}, {"State3", type text}, {"StreetAddress1", type text}, {"StreetAddress2", type text}, {"Suffix", type text}, {"TimeZone", type text}, {"Title", type text}, {"Username", type text}, {"Validated", Int64.Type}, {"Website", type text}, {"ZipFour1", type text}, {"ZipFour2", type text}, {"ZipFour3", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Id", "City", "Country", "DateCreated", "Email", "FirstName", "LastName", "LastUpdated", "LeadSourceId", "Leadsource", "PostalCode", "State"}),
    #"Inserted Date" = Table.AddColumn(#"Removed Other Columns", "Date", each DateTime.Date([DateCreated]), type date),
    #"Inserted Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([DateCreated]), type time),
    #"Added Test Contact Column" = Table.AddColumn(#"Inserted Time", "Test Contact", each if [Email] = null 
     then false
     else 
           if (Text.StartsWith([Email], "xxx") and Text.EndsWith([Email], "@gmail.com")) 
               or Text.EndsWith([Email], "@xxx.com")  
           then true 
           else false),
    #"Renamed Columns" = Table.RenameColumns(#"Added Test Contact Column",{{"DateCreated", "Created DateTime"}, {"Date", "Created Date"}, {"Time", "Created Time"}, {"Id", "ContactId"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ContactId", "Created DateTime", "Created Date", "Created Time", "Email", "FirstName", "LastName", "Leadsource", "LeadSourceId", "Country", "State", "City", "PostalCode", "LastUpdated"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Created DateTime", Order.Descending}})
in
    #"Sorted Rows"

The steps before #"Table Ready" normalize both data sources so that everything else is the same between them. I can switch the data source by uncommenting the relevant parts. But I would like it to be a little easier than that.

is there a way to add an if clause to the code like 'If use_dw = True then <steps for MYSQL> else <steps for CSV>'?

Thanks for your suggestions of how to implement this or other ideas.

[v-shex-msft]

HI @Kimcha,

I think you can try to use 'try otherwise' function with 'if statement' to check connector is available.

Regards,

XIaoxin Sheng

[ThomasDay]

Hello @v-shex-msft , @Kimcha , @MarcelBeug 

I have been building a large set of Power BI models with specific SQL DB's, CSV's, and XLXS file connections.  They all load from local DB's at the moment.

I need to make them more location independent. 

• So I'm redoing the queries so that there is each data source is isolated in a single query in each model.  Other queries will access that "source" query.  Easy enough.
• So I also now have two syncronized sets of data that reside on two different machines...and so would like to put both of the data locations in each query and just pick/change a parameter to have each query use the data set it is able to connect to.  This would enable the models themselves to be identical on each machine (outside of the pick parameter)...making code maintenance easier.

I need help getting started.  I read thru this post and feel certain that this will point the way.  I'm not very M saavy (rather am not M saavy) so don't know how to set the parameter use_dw referenced above...

Anyway, any chance you could give me a simple IF(use_dw) <use this source>, Else <use a different source> in M.

Thank you very much in advance,

Tom

[MarcelBeug]

If you adjust the name of 1 or both Source steps, so they are unique,

you can decomment all code and

replace the 2 #"Table Ready" steps by 1 #"Table Ready" step:

#"Table Ready" = if use_dw then #"Renamed infusionsoftid" else #"Promoted Headers",

Lazy evaluation in Power Query will take care that only the code will be evaluated that is required for the end result.

One small remark: if use_dw is a logical then if use_dw = true is the same as if use_dw

[jkuhlow]

I have done this very thing, switching between using blob storage or sql database based on a parameter.  Works great in desktop, but when published, refreshing is failing stating that it requires a gateway to be configured, even when the parameter is properly set to only pull data from the blob storage location.

Is the lazy evaluation only in desktop?

[Kimcha]

Awesome, thank you very much! Will try that on Monday.

And just to clarify as I am still not quite clear on how this will work, this would be the final code:

let
    SourceCSV = Csv.Document(File.Contents(data_path & "\is_sync\is_sync_latest\Contact.csv"),[Delimiter=",", Columns=80, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(SourceCSV, [PromoteAllScalars=true]),

// Data Warehouse Sync
    SourceDW = MySQL.Database(dw_host, dw_db, [ReturnSingleDatabase=true]),
    xxx_infusionsoft_table = SourceDW{[Schema="xxx",Item="infusionsoft_contacts"]}[Data],
    #"Removed DW Columns" = Table.RemoveColumns(xxx_infusionsoft_table,{"id", "last_synced", "needs_resync", "created", "modified"}),
    #"Fixed Header" = Table.TransformColumnNames(#"Removed DW Columns", (columnName as text) as text => Text.Replace(columnName, "inf_", "")),
//    #"Added LastUpdated" = Table.AddColumn(#"Fixed Header", "LastUpdated", each null),
//    #"Renamed infusionsoftid" = Table.RenameColumns(#"Added LastUpdated",{{"infusionsoftid", "Id"}}),
    #"Renamed infusionsoftid" = Table.RenameColumns(#"Fixed Header",{{"infusionsoftid", "Id"}}),

    #"Table Ready" = if use_dw then #"Renamed infusionsoftid" else #"Promoted Headers",
// Transform
    #"Changed Type" = Table.TransformColumnTypes(#"Table Ready",{{"Id", Int64.Type}, {"AccountId", Int64.Type}, {"Address1Type", type text}, {"Address2Street1", type text}, {"Address2Street2", type text}, {"Address2Type", type text}, {"Address3Street1", type text}, {"Address3Street2", type text}, {"Address3Type", type text}, {"Anniversary", type text}, {"AssistantName", type text}, {"AssistantPhone", type text}, {"BillingInformation", type text}, {"Birthday", type text}, {"City", type text}, {"City2", type text}, {"City3", type text}, {"Company", type text}, {"CompanyID", Int64.Type}, {"ContactNotes", type text}, {"ContactType", type text}, {"Country", type text}, {"Country2", type text}, {"Country3", type text}, {"CreatedBy", Int64.Type}, {"DateCreated", type datetime}, {"Email", type text}, {"EmailAddress2", type text}, {"EmailAddress3", type text}, {"Fax1", type text}, {"Fax1Type", type text}, {"Fax2", type text}, {"Fax2Type", type text}, {"FirstName", type text}, {"Groups", type number}, {"JobTitle", type text}, {"Language", type text}, {"LastName", type text}, {"LastUpdated", type datetime}, {"LastUpdatedBy", Int64.Type}, {"LeadSourceId", Int64.Type}, {"Leadsource", type text}, {"MiddleName", type text}, {"Nickname", type text}, {"OwnerID", Int64.Type}, {"Password", type text}, {"Phone1", type text}, {"Phone1Ext", type text}, {"Phone1Type", type text}, {"Phone2", type text}, {"Phone2Ext", type text}, {"Phone2Type", type text}, {"Phone3", type text}, {"Phone3Ext", type text}, {"Phone3Type", type text}, {"Phone4", type text}, {"Phone4Ext", type text}, {"Phone4Type", type text}, {"Phone5", type text}, {"Phone5Ext", type text}, {"Phone5Type", type text}, {"PostalCode", type text}, {"PostalCode2", type text}, {"PostalCode3", type text}, {"ReferralCode", type text}, {"SpouseName", type text}, {"State", type text}, {"State2", type text}, {"State3", type text}, {"StreetAddress1", type text}, {"StreetAddress2", type text}, {"Suffix", type text}, {"TimeZone", type text}, {"Title", type text}, {"Username", type text}, {"Validated", Int64.Type}, {"Website", type text}, {"ZipFour1", type text}, {"ZipFour2", type text}, {"ZipFour3", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Id", "City", "Country", "DateCreated", "Email", "FirstName", "LastName", "LastUpdated", "LeadSourceId", "Leadsource", "PostalCode", "State"}),
    #"Inserted Date" = Table.AddColumn(#"Removed Other Columns", "Date", each DateTime.Date([DateCreated]), type date),
    #"Inserted Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([DateCreated]), type time),
    #"Added Test Contact Column" = Table.AddColumn(#"Inserted Time", "Test Contact", each if [Email] = null 
     then false
     else 
           if (Text.StartsWith([Email], "xxx") and Text.EndsWith([Email], "@gmail.com")) 
               or Text.EndsWith([Email], "@xxx.com")  
           then true 
           else false),
    #"Renamed Columns" = Table.RenameColumns(#"Added Test Contact Column",{{"DateCreated", "Created DateTime"}, {"Date", "Created Date"}, {"Time", "Created Time"}, {"Id", "ContactId"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ContactId", "Created DateTime", "Created Date", "Created Time", "Email", "FirstName", "LastName", "Leadsource", "LeadSourceId", "Country", "State", "City", "PostalCode", "LastUpdated"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Created DateTime", Order.Descending}})
in
    #"Sorted Rows"

Meaning that even though the Table Ready step is AFTER both the DW and CSV sections, it will still only execute the one that is used in the end? 

[MarcelBeug]

Yes, that's correct. The code looks good to me.

When evaluating the code, Power Query will determine the order and necessity of execution.

The following query returns 3. Steps d and e are not evaluated, so the query won't return an error for step e.

(In the query editor you will have an error at step e, though, but that is no issue).

let
    d = 1,
    a = c,
    c = b,
    b = 8,
    e = Number.From("John Doe"),
    f = Number.Log(a,2)
in
    f