Create multiple queries from table values

[Anonymous]

On one hand I have a function that creates a table with multiple columns based on one value.   On the other I have a table in excel where the user can input new rows, each row has a new value to be used on the function.

I know how to use parameters so PQ reads the value from the Parameter table and uses it on a function to create a table.

The user does not know PQ to create a new table every time a new row is added to the excel table, so I need PQ to "iterate" through it and dinamically create all the tables on every refresh. 

Simplifying an example:

Excel Table

Data     Value

A            2

B            3

C            4

PQ function

=Data Value * 5

Query Result:

Table 1      Table 2      Table 3

10                15                 20

when the user adds new rows 5 tables should be created:

Data     Value

A            2

B            3

C            4

D            8

C           10

Table 1      Table 2         Table 3       Table 4         Table 5

10                15                 20               40                60

Thanks in advance for taking the time to help!

[AlB]

@Anonymous 

Why not have all shift in one table, one after the other, with potentially a column for the shiftID/number?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

I have no problem doing it in one table vs many, as you suggested an id would identify each shift in the table.  However,  the question is still there, how do I iterate though the table with all the codes and apply the funtion to each one in order to generate all the rows?

[AlB]

@Anonymous 

You can just create a custom column with the function, so that in each row the function reads the input values from the other column(s) and returns the table. You will then have that new column with a table in each row. Do a Table.Combine([Column]) and that will append all those tables into one table 

If you have some sample data (that input table) plus the function post it here and we can wrap it up.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

I see what you mean.  I'll try and get back to you, either solved or with the sample data so you can help me in case I can't do it on my own  (one learns more by doing, or at least trying, than copying 😉).

[AlB]

Hi @Anonymous 

Creating so many table makes little sense from a modelling point of view. Everything will be much easier if you have all that info in one single table. Can you explain what you are attempting to do? Perhaps we'll find a better solution

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Maybe I oversimplified my example.  Here is a detailed explanation:

We calculate payroll every week and, for each worker, I have to know if it's a labor day and the check in and out hour for that shift to compare against the time clock they use every day and calculate hours worked, overtime and bonus for excess overtime and working on sundays or days off.

To do so, I'm setting up working shifts and then asign to each worker.  Shifts are 48 hours per week, in different configurations: 5 or 6 days per week with one day off ,  4 days a week  with 3 days off and maybe other combinations in the future.  Also days off can vary from Monday to Sunday.   

My thought process is:

a) create a table with a code for each shift:

8.1Su1 = 8 hour shift, first shift, Sunday off

8.2Mo1 = 8 hour shift, second shift, Monday off

12.1Fr3= 12 hour shift, fist shift, Fri-Sunday Off   

This way when a new shift is created  a new code is entered in the table.

b) The employee database is updated with a shift code for every employee

c) I have a function that will take the code and create a table for each shift for the current year that includes Date, Day of week, Date/Time In, Date/Time Out, Shift, Hours, Type (Working day/Dayoff)

d) So far I can create a table for each shift invoking a funtion with the shift code.  Since the combination of shifts can be high, and I can't count on the user doing it and not missing one, I was thinking in iterating through the table to run the fuction for each row to get a table for each shift.   That way all they have to do is add the code in the excel table, assign the shift to the employee and refresh.

I hope I am clear but if you need further clarification please let me know.   

I'm just tarting to develop complex solutions (at least for me)  so, as you mention, there is probably a better way to achieve this.  I'm open to any and all suggestions to solve my problem.

As always, tahnks for taking the time to help.