topic Re: Dynamic SQL Tables in Power Query

Dynamic SQL Tables

dstanisljevic — Wed, 11 Apr 2018 20:49:30 GMT

I've been trying to find a method to automatically have my sql query add new tables to it. Our database generates a new table at the start of the month, resulting in having to update the project to now include that table. The table does end with "_YEARMO". Would it be possible to have a formula check for the first of the month and update the query to add a join for the new table?

Re: Dynamic SQL Tables

Greg_Deckler — Thu, 12 Apr 2018 03:01:03 GMT

In theory, you could have a function that does this and essentially returns a list of all of the tables that you want and another function or the main query itself just do a union/join on that list of table names. In theory. People have done similar things with web pages that essentially have an unspecified number of pages like ?page=1, ?page=2, etc. ImkeF might be able to help out here.

Re: Dynamic SQL Tables

ImkeF — Thu, 12 Apr 2018 05:12:19 GMT

Is my understanding correct, that the table name would end with _201804 for April?

Then you could create filter expression like this:

DateStartOfMonth = #date(Number.From( Text.Start( Text.End( FileName, 6), 4)) , Number.From( Text.End( FileName, 2)) ,1)

that returns the date of the first day in April.

You could then compare it to the current day:

if DateStartOfMonth >= Date.From(DateTime.LocalNow()) then ...

Re: Dynamic SQL Tables

dstanisljevic — Wed, 18 Apr 2018 15:49:13 GMT

ImkeF That is correct, the April table would end in that fashion. What I'm struggling with is making that part of the select statement when querying the data from that table. I've figured out how to do it via parameters, but this only works when utilizing the desktop app.

Re: Dynamic SQL Tables

ImkeF — Wed, 18 Apr 2018 15:56:21 GMT

That surprises me: What errror-message are you getting in the service?

Re: Dynamic SQL Tables

dstanisljevic — Wed, 18 Apr 2018 19:02:03 GMT

OK, worked the variable part out and it was my error. Here's what I have that is working for a basic test:

let

TableDate = "201804",

Source = Sql.Database("localhost", "Database", [Query="select RoomName,RoomDescription,AssetName,CFV_RawEnergyUsage_"&TableDate&".AssetID,CFV_RawEnergyUsage_"&TableDate&".EnergyUsage,CFV_RawEnergyUsage_"&TableDate&".LogTimeStamp#(lf)from CFV_RawEnergyUsage_"&TableDate&"#(lf)join CRV_Assets on CFV_RawEnergyUsage_"&TableDate&".AssetID=CRV_Assets.AssetID#(lf)join CRV_Symbols on CRV_Assets.SymbolID=CRV_Symbols.SymbolID#(lf)join CRV_Rooms on CRV_Symbols.RoomID=CRV_Rooms.RoomID"])

Source

So right now, just testing it as a fixed variable. This works and returns the table correctly. Where I'm struggling is what Greg_Deckler suggested, looping through to bring in multiple tables. So as new tables are generated, bring there data in as well as ensuring that the past tables are in there as well.

Re: Dynamic SQL Tables

dstanisljevic — Fri, 20 Apr 2018 21:28:06 GMT

Here is where I'm at. I'm very new to this so I'm assuming what I'm trying to do is more complex than my knowledge of BI. So I've been able to figure out how to calculate the date info I need into the correct format and then append the tables together into a table that gives me the data I need. Where I'm failing is understanding how to loop the process. Here's what I expect the process to be:

1) Determine start of month from current date. Grab data from that table.

2) Loop backwards and grab data from each table until the EndDate table is gathered.

3) End with all data into a single table.

Is this doable?

let
EndDate = "201701",
TableDate1 = Date.ToText(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),Date.Day(DateTime.LocalNow())), "yyyyMM"),
TableDate2 = Date.ToText(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()) - 1,Date.Day(DateTime.LocalNow())), "yyyyMM"),
Source1 = Sql.Database("localhost", "database", [Query="select RoomName,AssetName,CFV_SummarizedEnergyUsageByHour_"&TableDate1&".AssetID,CFV_SummarizedEnergyUsageByHour_"&TableDate1&".EnergyUsage,CFV_SummarizedEnergyUsageByHour_"&TableDate1&".LogTimeStamp#(lf)from CFV_SummarizedEnergyUsageByHour_"&TableDate1&"#(lf)join CRV_Assets on CFV_SummarizedEnergyUsageByHour_"&TableDate1&".AssetID=CRV_Assets.AssetID#(lf)join CRV_Symbols on CRV_Assets.SymbolID=CRV_Symbols.SymbolID#(lf)join CRV_Rooms on CRV_Symbols.RoomID=CRV_Rooms.RoomID"]),
Source2 = Sql.Database("localhost", "database", [Query="select RoomName,AssetName,CFV_SummarizedEnergyUsageByHour_"&TableDate2&".AssetID,CFV_SummarizedEnergyUsageByHour_"&TableDate2&".EnergyUsage,CFV_SummarizedEnergyUsageByHour_"&TableDate2&".LogTimeStamp#(lf)from CFV_SummarizedEnergyUsageByHour_"&TableDate2&"#(lf)join CRV_Assets on CFV_SummarizedEnergyUsageByHour_"&TableDate2&".AssetID=CRV_Assets.AssetID#(lf)join CRV_Symbols on CRV_Assets.SymbolID=CRV_Symbols.SymbolID#(lf)join CRV_Rooms on CRV_Symbols.RoomID=CRV_Rooms.RoomID"]),
Data = Table.Combine({Source1,Source2})
in
Data

Re: Dynamic SQL Tables

ImkeF — Sat, 21 Apr 2018 05:33:57 GMT

You don't have to loop explicitely in Power Query: Adding a column to a table in Power Query will execute a function (that you define in the last argument) that will be applied to every row. You then just expand that new column (if a table or record with multiple fields hast been created)

I've used a generic date-function from my blog that is pretty cody, so don't be scared by the amount of code, but once pasted into the advanced editor, you'll see that it's just a good handful of steps resulting ;-)

let

	DateFunction = let 
			// ----------------------- Documentation ----------------------- 
		documentation_ = [
		Documentation.Name =  " Dates.ListDateIntervals
		", Documentation.Description = " Creates a list of dates according to the chosen interval between Start and End. Allowed values for 3rd parameter: ""Year"", ""Quarter"", ""Month"", ""Week"" or ""Day"".
		" , Documentation.LongDescription = " Creates a list of dates according to the chosen interval between Start and End. The dates created will always be at the end of the interval, so could be in the future if today is chosen.
		", Documentation.Category = " Table
		", Documentation.Source = " http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ . 
		", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . 
		", Documentation.Examples = {[Description =  " see http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ .
		" , Code = " 
		 ", Result = " 
		"]}],

		// ----------------------- Function Code ----------------------- 
		function_ =  
		(From as date, To as date, optional Selection as text ) =>
		let
		// Create default-value "Day" if no selection for the 3rd parameter has been made
		TimeInterval = if Selection = null then "Day" else Selection,

		// Table with different values for each case
		CaseFunctions = #table({"Case", "LastDateInTI", "TypeOfAddedTI", "NumberOfAddedTIs"},
				{   {"Day", Date.From, Date.AddDays, Number.From(To-From)+1},
					{"Week", Date.EndOfWeek, Date.AddWeeks, Number.RoundUp((Number.From(To-From)+1)/7)},
					{"Month", Date.EndOfMonth, Date.AddMonths, (Date.Year(To)*12+Date.Month(To))-(Date.Year(From)*12+Date.Month(From))+1},
					{"Quarter", Date.EndOfQuarter, Date.AddQuarters, (Date.Year(To)*4+Date.QuarterOfYear(To))-(Date.Year(From)*4+Date.QuarterOfYear(From))+1},
					{"Year", Date.EndOfYear, Date.AddYears,Date.Year(To)-Date.Year(From)+1} 
				} ),

		// Filter table on selected case
				Case = CaseFunctions{[Case = TimeInterval]},
			
		// Create list with dates: List with number of date intervals -> Add number of intervals to From-parameter -> shift dates at the end of each respective interval	
			DateFunction = List.Transform({0..Case[NumberOfAddedTIs]-1}, each Function.Invoke(Case[LastDateInTI], {Function.Invoke(Case[TypeOfAddedTI], {From, _})}))
		in
			DateFunction,
		// ----------------------- New Function Type ----------------------- 
		type_ = type function (
			  From as (type date),
			  To as (type date),
			  optional Selection as (type text meta [
									Documentation.FieldCaption = "Select Date Interval",
									Documentation.FieldDescription = "Select Date Interval, if nothing selected, the default value will be ""Day""",
									Documentation.AllowedValues = {"Day", "Week", "Month", "Quarter", "Year"}
									])
				)
			as table meta documentation_,

		// Replace the extisting type of the function with the individually defined
		Result =  Value.ReplaceType(function_, type_)
	 in 
	Result,
    
	SQL_Function = (TableDate as text) =>

                    Sql.Database("localhost", "database", 
                    [Query="select RoomName,AssetName,CFV_SummarizedEnergyUsageByHour_" & TableDate & ".AssetID,CFV_SummarizedEnergyUsageByHour_" & TableDate & ".EnergyUsage,CFV_SummarizedEnergyUsageByHour_" & TableDate & ".LogTimeStamp
                    from CFV_SummarizedEnergyUsageByHour_" & TableDate & "
                    join CRV_Assets on CFV_SummarizedEnergyUsageByHour_" & TableDate & ".AssetID=CRV_Assets.AssetID
                    join CRV_Symbols on CRV_Assets.SymbolID=CRV_Symbols.SymbolID
                    join CRV_Rooms on CRV_Symbols.RoomID=CRV_Rooms.RoomID"]),


    ListOfDates = DateFunction(#date(2017,01,01), Date.From(DateTime.LocalNow()), "Month"),
    #"Converted to Table" = Table.FromList(ListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    FormattedDate = Table.AddColumn(#"Converted to Table", "Date", each Date.Year([Column1])*100 + Date.Month([Column1])),
    ChgType = Table.TransformColumnTypes(FormattedDate,{{"Date", type text}}),
    CallSQLFunction = Table.AddColumn(ChgType, "ExecuteSQLFunction", each SQL_Function([Date]))
in
    CallSQLFunction

I've also converted your SQL-Call to a function so that it can be applied to every row. Just expand the resulting column.