PowerQuery Mashup executable takes ridiculous amount of resource in RDS pool

Hi,

We have a Remote Desktop pool with 4 servers running Server 2012 RDS.

Whenever someone executes a powerquery the CPU and/or RAM of that server goes through the roof.
90-99% CPU usage for about 1 or 2 minutes.
Needless to say : the other users virtually can't do anything in the meanwhile.

I just updated Power Query this morning (to 2.25.4095.242) on 1 of the 4 servers : issue is the same.

So it's basically unworkable.

Thank you.

August 19th, 2015 1:03pm

Bump
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2015 4:03pm

Depending on the query, PQ can sometimes consume quite a bit of CPU or RAM.

Have you tried limiting resource usage by user or application?

https://social.technet.microsoft.com/Forums/windowsserver/en-US/f9c5b1a8-f35a-4c6d-8836-7f242adbc534/can-you-limit-cpu-usage-by-user-or-application

Ehren

August 24th, 2015 10:36pm

Thanks a lot Ehren,

But unfortunately this workaround was deprecated for server 2012 R2. 
This was an option until Server 2012.

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 9:35am

Ok. Seems like a question for the Remote Desktop team. Do you mind if I move this thread to that forum?

Ehren

August 26th, 2015 4:40pm

Well actually I do mind. I think it's a powerquery issue.

The fact that you can't cap it in RDS Server 2012 R2 obviously isn't helping, but I still don't think it's normal that a lot if not most of our Powerquery docs consume 99 CPU for 1-2 minutes. Isn't that a coding issue within PowerQuery ?

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 6:47am

Can you share a sample query that a user is running which causes this behavior?

Ehren

August 27th, 2015 5:11pm

What do you mean with a sample query?

I did some profiling on this environment on SQL server and it is doing some queries: do you want to see those? Cause my PowerQuery Excel contains over 20 queries.

I read something about query folding that in some cases is not happening, hence not performing most of the actions on the database server. Will this cause typically such out of memory issues and high CPU usage at the client machine?

I was wondering. I have here 5 tables with where clauses on it and so on. I checked SQL profiler and those actions are nicely sent to SQL server, so filtering, ... happens on SQL server.

But then I have my 6th powerquery (I have over 20 in it), but my 6th one combines all 5 together so I have in fact a union join. Will this prevent query folding, since I did not find any union joins in my SQL profiler, but could be because my out of memory error happens before union join is perfomed.

I am just asking, since I am doing union join on transactional data which is huge amount of data.

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 8:36pm

It certainly sounds like your sixth query (the one that combines the other five queries together) could be causing the high memory and CPU consumption. Are the tables that it's joining all from the same database, or different databases? Also, do you need to join them into a single dataset, or could you load them to the data model independently and use relationships to tie them together?

Ehren

August 31st, 2015 11:35pm

Tables are from same database.

I don't understand what you mean with "need to join them into a single dataset, or could you load them to the data model independently and use relationships to tie them together?"

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 4:00pm

Hi Patrick. There are multiple ways to relate data using Power Query and Power Pivot. You can merge/join the data in Power Query, prior to filling the data to an Excel sheet or the Excel data model. Or you can load each table independently into the Excel data model, and then use Power Pivot to define relationships between each of the tables.

Since the joining of the data in PQ is what seems to be killing the perf, it might be good to try loading the tables to the data model and then relating them in Power Pivot. It's just another way to accomplish the same thing, perhaps with better performance.

Ehren

September 8th, 2015 4:38pm

Hi Ehren:

I understood the request as a UNION-Operation, in Power Query: Append (Table.Combine)-operation.

As far as I'm aware, there is currently no function for that in Power Pivot / Data Model.

(The merge/(nested)join you are talking of is indeed the standard relationships between tables in PP/DataModel)

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 8:33pm

Ah, my mistake. A Table.Combine of the other queries should fold. Patrick, would it be possible for you to share the formulas for the queries (via Send a Frown if the contents are not shareable in the forum)? Trace logs from when the queries are refreshed would also help.

Thanks,

Ehren

September 8th, 2015 8:55pm

Hi Ehren,

I Send a frown. I copy-pasted set of formulas in here, but i am limited to number of characters in this post, so not all of them are in.

Formulas:

Formulas: section Section1; shared #"01_PROJITEMTRANS" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJITEMTRANS = Bron{[Schema="dbo",Item="PROJITEMTRANS"]}[Data], #"DATAAREAID Gefilterde rijen" = Table.SelectRows(dbo_PROJITEMTRANS, each [DATAAREAID] = DATAAREAID_Value), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"DATAAREAID Gefilterde rijen", each Text.Contains([#"PROJID"], Project_Value)), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Project-ID Gefilterde rijen",{"PROJID", "CATEGORYID", "TXT", "QTY", "CURRENCYID", "PROJTRANSID", "SALESUNIT", "DEFAULTDIMENSION", "ACTIVITYNUMBER", "TOTALCOSTAMOUNTCUR", "DATAAREAID"}), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"PROJID", "CATEGORYID", "CURRENCYID", "PROJTRANSID", "DEFAULTDIMENSION", "TOTALCOSTAMOUNTCUR", "TXT", "DATAAREAID"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"PROJID", "Project-ID"}, {"CATEGORYID", "Categorie"}, {"CURRENCYID", "Valuta"}, {"PROJTRANSID", "Transactie-ID"}, {"TOTALCOSTAMOUNTCUR", "Bedrag_TR_FC"}}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Namen van kolommen gewijzigd",{"Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd", "Transactietype", each "Artikel"), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd",{"Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd1",{{"TXT", "Omschrijving"}, {"QTY", "Hoeveelheid"}, {"SALESUNIT", "Eenheid"}}), #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Namen van kolommen gewijzigd1", "Type", each "Geboekt"), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "DATAAREAID"}) in #"Volgorde van kolommen gewijzigd3"; shared #"02_PROJCOSTTRANS" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJCOSTTRANS = Bron{[Schema="dbo",Item="PROJCOSTTRANS"]}[Data], #"DATAAREAID Gefilterde rijen" = Table.SelectRows(dbo_PROJCOSTTRANS, each [DATAAREAID] = DATAAREAID_Value), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"DATAAREAID Gefilterde rijen", each Text.Contains([#"PROJID"], Project_Value)), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Project-ID Gefilterde rijen",{"PROJID", "CATEGORYID", "TXT", "QTY", "TRANSID", "CURRENCYID", "DEFAULTDIMENSION", "ACTIVITYNUMBER", "TOTALCOSTAMOUNTCUR", "DATAAREAID"}), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"PROJID", "CATEGORYID", "TRANSID", "CURRENCYID", "DEFAULTDIMENSION", "TOTALCOSTAMOUNTCUR", "TXT", "DATAAREAID"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"PROJID", "Project-ID"}, {"CATEGORYID", "Categorie"}, {"TRANSID", "Transactie-ID"}, {"CURRENCYID", "Valuta"}, {"TOTALCOSTAMOUNTCUR", "Bedrag_TR_FC"}}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Namen van kolommen gewijzigd",{"Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Aangepaste kolom Transactietype toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd", "Transactietype", each "Onkosten"), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Aangepaste kolom Transactietype toegevoegd",{"Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd1",{{"TXT", "Omschrijving"}, {"QTY", "Hoeveelheid"}}), #"Type Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd1", "Type", each "Geboekt"), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Type Aangepaste kolom toegevoegd",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "DATAAREAID"}), #"Aangepaste kolom Eenheid toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd3", "Eenheid", each "") in #"Aangepaste kolom Eenheid toegevoegd"; shared #"03_PROJEMPLTRANS" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJEMPLTRANS = Bron{[Schema="dbo",Item="PROJEMPLTRANS"]}[Data], #"DATAAREAID Gefilterde rijen" = Table.SelectRows(dbo_PROJEMPLTRANS, each [DATAAREAID] = DATAAREAID_Value), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"DATAAREAID Gefilterde rijen", each Text.Contains([#"PROJID"], Project_Value)), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Project-ID Gefilterde rijen",{"PROJID", "CATEGORYID", "TXT", "QTY", "TRANSID", "CURRENCYID", "DEFAULTDIMENSION", "ACTIVITYNUMBER", "TOTALCOSTAMOUNTCUR", "DATAAREAID"}), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"PROJID", "CATEGORYID", "TRANSID", "CURRENCYID", "DEFAULTDIMENSION", "TOTALCOSTAMOUNTCUR", "TXT", "DATAAREAID"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd3",{{"PROJID", "Project-ID"}, {"CATEGORYID", "Categorie"}, {"TRANSID", "Transactie-ID"}, {"CURRENCYID", "Valuta"}}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Namen van kolommen gewijzigd",{"Project-ID", "Categorie", "Valuta", "Transactie-ID", "DEFAULTDIMENSION", "TOTALCOSTAMOUNTCUR", "DATAAREAID"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd",{{"TOTALCOSTAMOUNTCUR", "Bedrag_TR_FC"}}), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Namen van kolommen gewijzigd1",{"Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Aangepaste kolom Transactietype toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd1", "Transactietype", each "Uur"), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Aangepaste kolom Transactietype toegevoegd",{"Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Namen van kolommen gewijzigd2" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"TXT", "Omschrijving"}, {"QTY", "Hoeveelheid"}}), #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Namen van kolommen gewijzigd2", "Type", each "Geboekt"), #"Volgorde van kolommen gewijzigd4" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "DATAAREAID"}), #"Aangepaste kolom Eenheid toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd4", "Eenheid", each "Uur") in #"Aangepaste kolom Eenheid toegevoegd"; shared #"04_PROJONACCTRANS" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJONACCTRANS = Bron{[Schema="dbo",Item="PROJONACCTRANS"]}[Data], #"DATAAREAID Gefilterde rijen" = Table.SelectRows(dbo_PROJONACCTRANS, each [DATAAREAID] = DATAAREAID_Value), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"DATAAREAID Gefilterde rijen", each Text.Contains([#"PROJID"], Project_Value)), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Project-ID Gefilterde rijen",{"PROJID", "DESCRIPTION", "TRANSID", "CURRENCYID", "DEFAULTDIMENSION", "QTY", "TOTALSALESAMOUNTCUR", "ACTIVITYNUMBER", "DATAAREAID"}), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"PROJID", "TRANSID", "CURRENCYID", "DEFAULTDIMENSION", "TOTALSALESAMOUNTCUR", "DESCRIPTION", "DATAAREAID"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"PROJID", "Project-ID"}, {"TRANSID", "Transactie-ID"}, {"CURRENCYID", "Valuta"}, {"TOTALSALESAMOUNTCUR", "Bedrag_TR_FC"}}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Namen van kolommen gewijzigd",{"Project-ID", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "DATAAREAID"}), #"Aangepaste kolom Transactietype toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd", "Transactietype", each "A conto"), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Aangepaste kolom Transactietype toegevoegd",{{"DESCRIPTION", "Omschrijving"}, {"QTY", "Hoeveelheid"}}), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Namen van kolommen gewijzigd1",{"Transactietype", "Project-ID", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "Omschrijving", "DATAAREAID"}), #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Volgorde van kolommen gewijzigd3", "Type", each "Geboekt"), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "Omschrijving", "DATAAREAID"}), #"Aangepaste kolom Eenheid toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd1", "Eenheid", each "") in #"Aangepaste kolom Eenheid toegevoegd"; shared PROJCATEGORY = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJCATEGORY = Bron{[Schema="dbo",Item="PROJCATEGORY"]}[Data], #"Andere kolommen verwijderd" = Table.SelectColumns(dbo_PROJCATEGORY,{"CATEGORYID", "NAME", "CATEGORYGROUPID", "DATAAREAID"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Andere kolommen verwijderd",{{"CATEGORYID", "Categorie"}, {"NAME", "Categorienaam"}, {"CATEGORYGROUPID", "Categoriegroep"}}), #"DATAAREAID Gefilterde rijen" = Table.SelectRows(#"Namen van kolommen gewijzigd", each Text.Contains([DATAAREAID], DATAAREAID_Value)) in #"DATAAREAID Gefilterde rijen"; shared PROJTABLE = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJTABLE = Bron{[Schema="dbo",Item="PROJTABLE"]}[Data], #"Namen van kolommen gewijzigd" = Table.RenameColumns(dbo_PROJTABLE,{{"PROJID", "Project-ID"}, {"NAME", "Projectnaam"}}), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Namen van kolommen gewijzigd",{"Project-ID", "Projectnaam", "DATAAREAID"}), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"Andere kolommen verwijderd", each Text.Contains([#"Project-ID"], Project_Value)), #"DATAAREAID Gefilterde rijen" = Table.SelectRows(#"Project-ID Gefilterde rijen", each [DATAAREAID] = DATAAREAID_Value) in #"DATAAREAID Gefilterde rijen"; shared #"05_PROJREVENUETRANS" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJREVENUETRANS = Bron{[Schema="dbo",Item="PROJREVENUETRANS"]}[Data], #"DATAAREAID Gefilterde rijen" = Table.SelectRows(dbo_PROJREVENUETRANS, each [DATAAREAID] = DATAAREAID_Value), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"DATAAREAID Gefilterde rijen", each Text.Contains([#"PROJID"], Project_Value)), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Project-ID Gefilterde rijen",{"PROJID", "CATEGORYID", "TXT", "CURRENCYID", "TRANSID", "QTY", "DEFAULTDIMENSION", "TOTALSALESAMOUNTCUR", "DATAAREAID", "ACTIVITYNUMBER"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Andere kolommen verwijderd",{{"PROJID", "Project-ID"}, {"CATEGORYID", "Categorie"}, {"CURRENCYID", "Valuta"}, {"TRANSID", "Transactie-ID"}, {"TOTALSALESAMOUNTCUR", "Bedrag_TR_FC"}}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Namen van kolommen gewijzigd",{"Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "TXT", "DATAAREAID"}), #"Aangepaste kolom Transactietype toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd", "Transactietype", each "Bijzondere kosten"), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Aangepaste kolom Transactietype toegevoegd",{"Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "TXT", "DATAAREAID"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd1",{{"TXT", "Omschrijving"}, {"QTY", "Hoeveelheid"}}), #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Namen van kolommen gewijzigd1", "Type", each "Geboekt"), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "DEFAULTDIMENSION", "Omschrijving", "DATAAREAID"}), #"Aangepaste kolom Eenheid toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd2", "Eenheid", each "") in #"Aangepaste kolom Eenheid toegevoegd"; shared AlleTransacties = let Bron = Table.Combine({#"01_PROJITEMTRANS",#"02_PROJCOSTTRANS"}), #"PROJEMPLTRANS Toevoegen" = Table.Combine({Bron,#"03_PROJEMPLTRANS"}), #"PROJONACCTRANS Toevoegen" = Table.Combine({#"PROJEMPLTRANS Toevoegen",#"04_PROJONACCTRANS"}), #"PROJREVENUETRANS Toevoegen" = Table.Combine({#"PROJONACCTRANS Toevoegen",#"05_PROJREVENUETRANS"}) in #"PROJREVENUETRANS Toevoegen"; shared Activiteit = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_DEFAULTDIMENSIONVIEW = Bron{[Schema="dbo",Item="DEFAULTDIMENSIONVIEW"]}[Data], #"Gefilterde rijen" = Table.SelectRows(dbo_DEFAULTDIMENSIONVIEW, each ([NAME] = "Activiteit")) in #"Gefilterde rijen"; shared PROJTRANSPOSTING = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJTRANSPOSTING = Bron{[Schema="dbo",Item="PROJTRANSPOSTING"]}[Data], #"DATAAREA_ID Gefilterde rijen" = Table.SelectRows(dbo_PROJTRANSPOSTING, each [DATAAREAID] = DATAAREAID_Value), #"Project-ID Gefilterde rijen" = Table.SelectRows(#"DATAAREA_ID Gefilterde rijen", each Text.Contains([PROJID], Project_Value)), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Project-ID Gefilterde rijen",{"LEDGERTRANSDATE", "PROJID", "VOUCHER", "TRANSID", "PROJTRANSTYPE", "AMOUNTMST", "DATAAREAID"}), #"PROJTRANSTYPE Type gewijzigd" = Table.TransformColumnTypes(#"Andere kolommen verwijderd",{{"TRANSID", type text}, {"PROJTRANSTYPE", type text}}), #"Onkosten Waarde vervangen" = Table.ReplaceValue(#"PROJTRANSTYPE Type gewijzigd","3","Onkosten",Replacer.ReplaceText,{"PROJTRANSTYPE"}), #"Uur Waarde vervangen" = Table.ReplaceValue(#"Onkosten Waarde vervangen","2","Uur",Replacer.ReplaceText,{"PROJTRANSTYPE"}), #"Artikel Waarde vervangen" = Table.ReplaceValue(#"Uur Waarde vervangen","4","Artikel",Replacer.ReplaceText,{"PROJTRANSTYPE"}), #"A conto Waarde vervangen" = Table.ReplaceValue(#"Artikel Waarde vervangen","5","A conto",Replacer.ReplaceText,{"PROJTRANSTYPE"}), #"Bijzonder kosten Waarde vervangen" = Table.ReplaceValue(#"A conto Waarde vervangen","1","Bijzondere kosten",Replacer.ReplaceText,{"PROJTRANSTYPE"}), #"Jaar ingevoegd" = Table.AddColumn(#"Bijzonder kosten Waarde vervangen", "Year", each Date.Year([LEDGERTRANSDATE]), type number), #"Kwartaal ingevoegd" = Table.AddColumn(#"Jaar ingevoegd", "Quarter", each Date.QuarterOfYear([LEDGERTRANSDATE]), type number), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Kwartaal ingevoegd",{{"LEDGERTRANSDATE", "Grootboekdatum"}, {"Year", "Jaar"}, {"Quarter", "Kwartaal"}}) in #"Namen van kolommen gewijzigd"; shared AlleTransactiesUitgebreidBasis = let Bron = Table.NestedJoin(AlleTransacties,{"Categorie", "DATAAREAID"},PROJCATEGORY,{"Categorie", "DATAAREAID"},"NewColumn"), #"PROJCATEGORY uitvouwen" = Table.ExpandTableColumn(Bron, "NewColumn", {"Categorienaam", "Categoriegroep"}, {"PROJCATEGORY.Categorienaam", "PROJCATEGORY.Categoriegroep"}), #"AllePrognoses Toevoegen" = Table.Combine({#"PROJCATEGORY uitvouwen",AllePrognoses}), #"PROJTABLE Samenvoegen" = Table.NestedJoin(#"AllePrognoses Toevoegen",{"Project-ID", "DATAAREAID"},PROJTABLE,{"Project-ID", "DATAAREAID"},"NewColumn"), #"PROJTABLE uitvouwen" = Table.ExpandTableColumn(#"PROJTABLE Samenvoegen", "NewColumn", {"Projectnaam"}, {"PROJTABLE.Projectnaam"}), #"AlleFinDimensies Samenvoegen" = Table.NestedJoin(#"PROJTABLE uitvouwen",{"DEFAULTDIMENSION"},AlleFinDimensies,{"DEFAULTDIMENSION"},"NewColumn"), #"AlleFinDimensies uitvouwen" = Table.ExpandTableColumn(#"AlleFinDimensies Samenvoegen", "NewColumn", {"Activiteit", "Kostcode", "Kostcodenaam", "Kostcodegroep", "Activiteitnaam"}, {"AlleFinDimensies.Activiteit", "AlleFinDimensies.Kostcode", "AlleFinDimensies.Kostcodenaam", "AlleFinDimensies.Kostcodegroep", "AlleFinDimensies.Activiteitnaam"}), #"Grootboektransacties Samenvoegen" = Table.NestedJoin(#"AlleFinDimensies uitvouwen",{"Transactie-ID"},Grootboektransacties,{"TRANSID"},"NewColumn"), #"Grootboektransacties uitvouwen" = Table.ExpandTableColumn(#"Grootboektransacties Samenvoegen", "NewColumn", {"Grootboekdatum", "Jaar", "Kwartaal", "VENDTABLECUBE.NAME", "BedragProjTransPosting"}, {"Grootboektransacties.Grootboekdatum", "Grootboektransacties.Jaar", "Grootboektransacties.Kwartaal", "Grootboektransacties.VENDTABLECUBE.NAME", "Grootboektransacties.BedragProjTransPosting"}), #"Onderaannemingscontract samengevoegd" = Table.NestedJoin(#"Grootboektransacties uitvouwen",{"ACTIVITYNUMBER", "DATAAREAID"},Onderaannemingscontract,{"SMMACTIVITYNUMBER", "DATAAREAID"},"NewColumn"), #"Onderaannemingscontract uitgevouwen" = Table.ExpandTableColumn(#"Onderaannemingscontract samengevoegd", "NewColumn", {"PBMWBSHeader.ORDERID", "PBMSubcontractor.NAME", "PBMSubcontractor.VENDACCOUNT"}, {"Onderaannemingscontract.PBMWBSHeader.ORDERID", "Onderaannemingscontract.PBMSubcontractor.NAME", "Onderaannemingscontract.PBMSubcontractor.VENDACCOUNT"}), #"Categorietype Aangepaste kolom toegevoegd" = Table.AddColumn(#"Onderaannemingscontract uitgevouwen", "Categorietype", each if Text.Range([Categorie],0,5)="WE-10" then "MATERIAAL" else if Text.Range([Categorie],0,5)="WE-20" then "ONDERAANNEMING" else if Text.Range([Categorie],0,5)="WE-30" then "MATERIEEL" else if Text.Range([Categorie],0,5)="WE-40" then "LONEN" else "OMZET"), #"Bedrag Aangepaste kolom toegevoegd" = Table.AddColumn(#"Categorietype Aangepaste kolom toegevoegd", "Bedrag", each if [Grootboektransacties.BedragProjTransPosting] is null then [Bedrag_TR_FC] else [Grootboektransacties.BedragProjTransPosting]), #"PBMWBSLINE samengevoegd" = Table.NestedJoin(#"Bedrag Aangepaste kolom toegevoegd",{"ACTIVITYNUMBER", "DATAAREAID"},PBMWBSLINE,{"SMMACTIVITYNUMBER", "DATAAREAID"},"NewColumn"), #"PBMWBSLINE uitgevouwen" = Table.ExpandTableColumn(#"PBMWBSLINE samengevoegd", "NewColumn", {"DESCRIPTION", "FULLPATH", "WBSTYPE", "ACMORIGINALLINENUM"}, {"PBMWBSLINE.DESCRIPTION", "PBMWBSLINE.FULLPATH", "PBMWBSLINE.WBSTYPE", "PBMWBSLINE.ACMORIGINALLINENUM"}), #"WBSTYPE Gefilterde rijen" = Table.SelectRows(#"PBMWBSLINE uitgevouwen", each [PBMWBSLINE.WBSTYPE] = 1 or [PBMWBSLINE.WBSTYPE] = null), #"CONACTIVITYCALCULATIONVARIABLE samengevoegd" = Table.NestedJoin(#"WBSTYPE Gefilterde rijen",{"ACTIVITYNUMBER", "DATAAREAID"},CONACTIVITYCALCULATIONVARIABLE,{"ACTIVITYNUMBER", "DATAAREAID"},"NewColumn"), #"CONACTIVITYCALCULATIONVARIABLE uitgevouwen" = Table.ExpandTableColumn(#"CONACTIVITYCALCULATIONVARIABLE samengevoegd", "NewColumn", {"VALUE"}, {"CONACTIVITYCALCULATIONVARIABLE.VALUE"}), #"SMMACTIVITIES samengevoegd" = Table.NestedJoin(#"CONACTIVITYCALCULATIONVARIABLE uitgevouwen",{"ACTIVITYNUMBER", "DATAAREAID"},SMMACTIVITIES,{"ACTIVITYNUMBER", "DATAAREAID"},"NewColumn"), #"SMMACTIVITIES uitgevouwen" = Table.ExpandTableColumn(#"SMMACTIVITIES samengevoegd", "NewColumn", {"CONCALCGROUPRECID"}, {"SMMACTIVITIES.CONCALCGROUPRECID"}), #"CONCALCULATIONGROUPJOIN samengevoegd" = Table.NestedJoin(#"SMMACTIVITIES uitgevouwen",{"SMMACTIVITIES.CONCALCGROUPRECID", "DATAAREAID"},CONCALCULATIONGROUPJOIN,{"CALCULATIONGROUPID", "DATAAREAID"},"NewColumn"), #"CONCALCULATIONGROUPJOIN uitgevouwen" = Table.ExpandTableColumn(#"CONCALCULATIONGROUPJOIN samengevoegd", "NewColumn", {"UNITOFMEASURE"}, {"CONCALCULATIONGROUPJOIN.UNITOFMEASURE"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"CONCALCULATIONGROUPJOIN uitgevouwen",{{"PBMWBSLINE.DESCRIPTION", "Omschrijving taak"}, {"CONCALCULATIONGROUPJOIN.UNITOFMEASURE", "Eenheid taak"}, {"CONACTIVITYCALCULATIONVARIABLE.VALUE", "Hoeveelheid taak"},{"PBMWBSLINE.ACMORIGINALLINENUM","Postnummer"}}), #"PBMWBSLINE.FULLPATH gesorteerd" = Table.Sort(#"Namen van kolommen gewijzigd",{{"PBMWBSLINE.FULLPATH", Order.Ascending}}) in #"PBMWBSLINE.FULLPATH gesorteerd"; shared Kostcode = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_DEFAULTDIMENSIONVIEW = Bron{[Schema="dbo",Item="DEFAULTDIMENSIONVIEW"]}[Data], #"Gefilterde rijen" = Table.SelectRows(dbo_DEFAULTDIMENSIONVIEW, each ([NAME] = "KostCode")) in #"Gefilterde rijen"; shared DIMATTRIBUTECONCOSTCODE = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_DIMATTRIBUTECONCOSTCODE = Bron{[Schema="dbo",Item="DIMATTRIBUTECONCOSTCODE"]}[Data] in dbo_DIMATTRIBUTECONCOSTCODE; shared DIMENSIONFINANCIALTAG = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_DIMENSIONFINANCIALTAG = Bron{[Schema="dbo",Item="DIMENSIONFINANCIALTAG"]}[Data] in dbo_DIMENSIONFINANCIALTAG; shared AlleFinDimensies = let Bron = Table.NestedJoin(Activiteit,{"DEFAULTDIMENSION"},Kostcode,{"DEFAULTDIMENSION"},"NewColumn"), #"NewColumn uitvouwen" = Table.ExpandTableColumn(Bron, "NewColumn", {"DISPLAYVALUE"}, {"DISPLAYVALUE.1"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"NewColumn uitvouwen",{{"DISPLAYVALUE.1", "Kostcode"}, {"DISPLAYVALUE", "Activiteit"}}), Samenvoegen = Table.NestedJoin(#"Namen van kolommen gewijzigd",{"Kostcode"},DIMATTRIBUTECONCOSTCODE,{"VALUE"},"NewColumn"), #"NewColumn uitvouwen1" = Table.ExpandTableColumn(Samenvoegen, "NewColumn", {"NAME"}, {"NAME.1"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"NewColumn uitvouwen1",{{"NAME.1", "Kostcodenaam"}}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd1", "Kostcodegroep", each Text.Range([Kostcode],0,2)), Samenvoegen1 = Table.NestedJoin(#"Aangepaste kolom toegevoegd",{"Activiteit"},DIMENSIONFINANCIALTAG,{"VALUE"},"NewColumn"), #"NewColumn uitvouwen2" = Table.ExpandTableColumn(Samenvoegen1, "NewColumn", {"DESCRIPTION"}, {"DESCRIPTION"}), #"Namen van kolommen gewijzigd2" = Table.RenameColumns(#"NewColumn uitvouwen2",{{"DESCRIPTION", "Activiteitnaam"}}) in #"Namen van kolommen gewijzigd2"; shared #"01_FORECASTSALES" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_FORECASTSALES = Bron{[Schema="dbo",Item="FORECASTSALES"]}[Data], #"PROJID Gefilterde rijen" = Table.SelectRows(dbo_FORECASTSALES, each Text.StartsWith([PROJID], Project_Value)), #"DATAAREAID Gefilterde rijen" = Table.SelectRows(#"PROJID Gefilterde rijen", each [DATAAREAID] = DATAAREAID_Value), #"Andere kolommen verwijderd" = Table.SelectColumns(#"DATAAREAID Gefilterde rijen",{"CURRENCY", "SALESQTY", "SALESUNITID", "COSTPRICE", "MODELID", "DEFAULTDIMENSION", "PROJID", "PROJCATEGORYID", "PROJTRANSID", "ACTIVITYNUMBER", "DATAAREAID", "ACMDESCRIPTION", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"MODELID", "CURRENCY", "SALESQTY", "COSTPRICE", "DEFAULTDIMENSION", "PROJID", "PROJCATEGORYID", "PROJTRANSID", "DATAAREAID", "ACMDESCRIPTION", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd",{{"MODELID", "Type"}}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Namen van kolommen gewijzigd",{{"PROJID", "Project-ID"}}), #"Namen van kolommen gewijzigd2" = Table.RenameColumns(#"Namen van kolommen gewijzigd1",{{"PROJCATEGORYID", "Categorie"}, {"CURRENCY", "Valuta"}, {"SALESQTY", "Hoeveelheid"}}), #"Namen van kolommen gewijzigd3" = Table.RenameColumns(#"Namen van kolommen gewijzigd2",{{"PROJTRANSID", "Transactie-ID"}}), #"Namen van kolommen gewijzigd4" = Table.RenameColumns(#"Namen van kolommen gewijzigd3",{{"ACMDESCRIPTION", "Omschrijving"}}), #"Namen van kolommen gewijzigd6" = Table.RenameColumns(#"Namen van kolommen gewijzigd4",{{"ADUFORECASTORIGINTYPE", "Prognose oorsprong type"}, {"SALESUNITID", "Eenheid"}}), #"Transactietype Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd6", "Transactietype", each "Artikel"), #"Volgorde van kolommen gewijzigd6" = Table.ReorderColumns(#"Transactietype Aangepaste kolom toegevoegd",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Omschrijving", "DEFAULTDIMENSION", "ADUWBSLINENUMORIGINAL", "Prognose oorsprong type", "Hoeveelheid", "COSTPRICE", "DATAAREAID"}), #"Bedrag_TR_FC Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Volgorde van kolommen gewijzigd6", "Bedrag_TR_FC", each [Hoeveelheid]*[COSTPRICE]), #"Volgorde van kolommen gewijzigd7" = Table.ReorderColumns(#"Bedrag_TR_FC Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "ADUWBSLINENUMORIGINAL", "Prognose oorsprong type", "DATAAREAID", "Hoeveelheid", "COSTPRICE"}) in #"Volgorde van kolommen gewijzigd7"; shared #"02_PROJFORECASTCOST" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJFORECASTCOST = Bron{[Schema="dbo",Item="PROJFORECASTCOST"]}[Data], #"PROJID Gefilterde rijen" = Table.SelectRows(dbo_PROJFORECASTCOST, each Text.StartsWith([PROJID], Project_Value)), #"DATAAREAID Gefilterde rijen" = Table.SelectRows(#"PROJID Gefilterde rijen", each [DATAAREAID] = DATAAREAID_Value), #"Andere kolommen verwijderd" = Table.SelectColumns(#"DATAAREAID Gefilterde rijen",{"PROJID", "CATEGORYID", "QTY", "COSTPRICE", "MODELID", "CURRENCYID", "TXT", "TRANSID", "DEFAULTDIMENSION", "ACTIVITYNUMBER", "DATAAREAID", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE"}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"MODELID", "PROJID", "CATEGORYID", "QTY", "COSTPRICE", "CURRENCYID", "TXT", "TRANSID", "DATAAREAID", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd",{{"MODELID", "Type"}, {"QTY", "Hoeveelheid"}}), #"Transactietype Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Transactietype", each "Onkosten"), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Transactietype Aangepaste kolom toegevoegd",{"Type", "Transactietype", "PROJID", "CATEGORYID", "Hoeveelheid", "COSTPRICE", "CURRENCYID", "TXT", "TRANSID", "DATAAREAID", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd1",{{"PROJID", "Project-ID"}, {"CATEGORYID", "Categorie"}}), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Namen van kolommen gewijzigd1",{"Type", "Transactietype", "Project-ID", "Categorie", "CURRENCYID", "Hoeveelheid", "COSTPRICE", "TXT", "TRANSID", "DATAAREAID", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE"}), #"Namen van kolommen gewijzigd2" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"CURRENCYID", "Valuta"}}), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Namen van kolommen gewijzigd2",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "TRANSID", "Hoeveelheid", "COSTPRICE", "TXT", "DATAAREAID", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE"}), #"Namen van kolommen gewijzigd3" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd3",{{"TRANSID", "Transactie-ID"}}), #"Bedrag_TR_FC Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Namen van kolommen gewijzigd3", "Bedrag_TR_FC", each [Hoeveelheid]*[COSTPRICE]), #"Volgorde van kolommen gewijzigd4" = Table.ReorderColumns(#"Bedrag_TR_FC Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "TXT", "Hoeveelheid", "COSTPRICE", "DATAAREAID", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE"}), #"Namen van kolommen gewijzigd4" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd4",{{"TXT", "Omschrijving"}}), #"Volgorde van kolommen gewijzigd5" = Table.ReorderColumns(#"Namen van kolommen gewijzigd4",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "ADUWBSLINENUMORIGINAL", "ADUFORECASTORIGINTYPE", "Hoeveelheid", "COSTPRICE", "DATAAREAID"}), #"Namen van kolommen gewijzigd5" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd5",{{"ADUFORECASTORIGINTYPE", "Prognose oorsprong type"}}), #"Volgorde van kolommen gewijzigd6" = Table.ReorderColumns(#"Namen van kolommen gewijzigd5",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "ADUWBSLINENUMORIGINAL", "Prognose oorsprong type", "DATAAREAID", "Hoeveelheid", "COSTPRICE"}), #"Aangepaste kolom Eenheid toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd6", "Eenheid", each "") in #"Aangepaste kolom Eenheid toegevoegd"; shared #"03_PROJFORECASTEMPL" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJFORECASTEMPL = Bron{[Schema="dbo",Item="PROJFORECASTEMPL"]}[Data], #"PROJID Gefilterde rijen" = Table.SelectRows(dbo_PROJFORECASTEMPL, each Text.StartsWith([PROJID], Project_Value)), #"DATAAREAID Gefilterde rijen" = Table.SelectRows(#"PROJID Gefilterde rijen", each [DATAAREAID] = DATAAREAID_Value), #"Andere kolommen verwijderd" = Table.SelectColumns(#"DATAAREAID Gefilterde rijen",{"PROJID", "CATEGORYID", "QTY", "COSTPRICE", "MODELID", "DEFAULTDIMENSION", "TXT", "CURRENCYID", "ACTIVITYNUMBER", "TRANSID", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"MODELID", "PROJID", "CATEGORYID", "COSTPRICE", "DEFAULTDIMENSION", "TXT", "CURRENCYID", "TRANSID", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd",{{"MODELID", "Type"}}), #"Transactietype Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Transactietype", each "Uur"), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Transactietype Aangepaste kolom toegevoegd",{{"PROJID", "Project-ID"}, {"CATEGORYID", "Categorie"}}), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Namen van kolommen gewijzigd1",{"Type", "Project-ID", "Categorie", "CURRENCYID", "COSTPRICE", "DEFAULTDIMENSION", "TXT", "TRANSID", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL", "Transactietype"}), #"Namen van kolommen gewijzigd2" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd1",{{"CURRENCYID", "Valuta"}}), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Namen van kolommen gewijzigd2",{"Type", "Project-ID", "Categorie", "Valuta", "TRANSID", "COSTPRICE", "DEFAULTDIMENSION", "TXT", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL", "Transactietype"}), #"Namen van kolommen gewijzigd3" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"TRANSID", "Transatie-ID"}}), #"Bedrag_TR_FC Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Namen van kolommen gewijzigd3", "Bedrag_TR_FC", each [COSTPRICE]*[QTY]), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Bedrag_TR_FC Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transatie-ID", "Bedrag_TR_FC", "TXT", "DEFAULTDIMENSION", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL", "QTY", "COSTPRICE"}), #"Namen van kolommen gewijzigd4" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd3",{{"TXT", "Omschrijving"}}), #"Volgorde van kolommen gewijzigd4" = Table.ReorderColumns(#"Namen van kolommen gewijzigd4",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transatie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "ADUWBSLINENUMORIGINAL", "DATAAREAID", "ADUFORECASTORIGINTYPE", "QTY", "COSTPRICE"}), #"Namen van kolommen gewijzigd5" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd4",{{"ADUFORECASTORIGINTYPE", "Prognose oorsprong type"}, {"QTY", "Hoeveelheid"}}), #"Volgorde van kolommen gewijzigd5" = Table.ReorderColumns(#"Namen van kolommen gewijzigd5",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transatie-ID", "Bedrag_TR_FC", "Omschrijving", "DEFAULTDIMENSION", "ADUWBSLINENUMORIGINAL", "Prognose oorsprong type", "DATAAREAID", "Hoeveelheid", "COSTPRICE"}), #"Aangepaste kolom Eenheid toegevoegd" = Table.AddColumn(#"Volgorde van kolommen gewijzigd5", "Eenheid", each "Uur") in #"Aangepaste kolom Eenheid toegevoegd"; shared #"04_PROJFORECASTONACC" = let //Parameters Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], Server_Value = Parameters{0}[Value], DB_Value = Parameters{1}[Value], Project_Value = Parameters{2}[Value], DATAAREAID_Value = Parameters{3}[Value], //Parameters Bron = Sql.Database(Server_Value, DB_Value), dbo_PROJFORECASTONACC = Bron{[Schema="dbo",Item="PROJFORECASTONACC"]}[Data], #"PROJID Gefilterde rijen" = Table.SelectRows(dbo_PROJFORECASTONACC, each Text.Contains([#"PROJID"], Project_Value)), #"DATAAREAID Gefilterde rijen" = Table.SelectRows(#"PROJID Gefilterde rijen", each [DATAAREAID] = DATAAREAID_Value), #"Andere kolommen verwijderd" = Table.SelectColumns(#"DATAAREAID Gefilterde rijen",{"PROJID", "SALESPRICE", "MODELID", "CURRENCYID", "TXT", "TRANSID", "DEFAULTDIMENSION", "QTY", "DATAAREAID", "ACTIVITYNUMBER", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Andere kolommen verwijderd",{"MODELID", "PROJID", "SALESPRICE", "CURRENCYID", "TXT", "TRANSID", "DEFAULTDIMENSION", "QTY", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd",{{"MODELID", "Type"}}), #"Transactietype Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Transactietype", each "A conto"), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Transactietype Aangepaste kolom toegevoegd",{"Type", "Transactietype", "PROJID", "SALESPRICE", "CURRENCYID", "TXT", "TRANSID", "DEFAULTDIMENSION", "QTY", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd1",{{"PROJID", "Project-ID"}}), #"Categorie Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Namen van kolommen gewijzigd1", "Categorie", each ""), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Categorie Aangepaste kolom toegevoegd1",{"Type", "Transactietype", "Project-ID", "Categorie", "CURRENCYID", "SALESPRICE", "TXT", "TRANSID", "DEFAULTDIMENSION", "QTY", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Namen van kolommen gewijzigd2" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd2",{{"CURRENCYID", "Valuta"}}), #"Volgorde van kolommen gewijzigd3" = Table.ReorderColumns(#"Namen van kolommen gewijzigd2",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "TRANSID", "SALESPRICE", "TXT", "DEFAULTDIMENSION", "QTY", "DATAAREAID", "ADUFORECASTORIGINTYPE", "ADUWBSLINENUMORIGINAL"}), #"Namen van kolommen gewijzigd3" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd3",{{"TRANSID", "Transactie-ID"}}), #"Bedrag_TR_FC Aangepaste kolom toegevoegd2" = Table.AddColumn(#"Namen van kolommen gewijzigd3", "Bedrag_TR_FC", each [SALESPRICE]*[QTY]), #"Volgorde van kolommen gewijzigd4" = Table.ReorderColumns(#"Bedrag_TR_FC Aangepaste kolom toegevoegd2",{"Type", "Transactietype", "Project-ID", "Categorie", "Valuta", "Transactie-ID", "Bedrag_TR_FC", "TXT", "DEFAULTDIMENSION", "DATAAREAID", "ADUFORECASTORIGINTYPE", "QTY", 

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 3:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics