cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kimcha Frequent Visitor
Frequent Visitor

Power Query Conditional Data Source

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.

4 REPLIES 4
MarcelBeug Super Contributor
Super Contributor

Re: Power Query Conditional Data Source

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

Specializing in Power Query Formula Language (M)
Kimcha Frequent Visitor
Frequent Visitor

Re: Power Query Conditional Data Source

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 Super Contributor
Super Contributor

Re: Power Query Conditional Data Source

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
Specializing in Power Query Formula Language (M)
Community Support Team
Community Support Team

Re: Power Query Conditional Data Source

HI @Kimcha,

 

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

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 321 members 3,147 guests
Please welcome our newest community members: