Greetings,

is it possible to place a normal Excel function into a Column in Power Query, so that it works as a function in the resulting data?

Simple Example:

As part of the data, I have piece prices and volumes from which we calculate to get the total price (price * volume = Total). Thus, I want PQ to create (among everything else) 3 Columns "price", "volume" and "total", with the raw data in the first two columns, but the formula "=[price]*[volume]" (or any excel formula) in the last column.

I have tried it, but all methods just return the function as text. Entering the cell and hitting return activates the function, but no one wants to do that for thousands of rows.

I am aware that this somewhat contradicts the purpose of PQ, but I have cases where other users copy individual lines from the data, and they want the dynamic function in case they need to test different prices or volumes.

Cheers,

Jamie

]]>is it possible to place a normal Excel function into a Column in Power Query, so that it works as a function in the resulting data?

Simple Example:

As part of the data, I have piece prices and volumes from which we calculate to get the total price (price * volume = Total). Thus, I want PQ to create (among everything else) 3 Columns "price", "volume" and "total", with the raw data in the first two columns, but the formula "=[price]*[volume]" (or any excel formula) in the last column.

I have tried it, but all methods just return the function as text. Entering the cell and hitting return activates the function, but no one wants to do that for thousands of rows.

I am aware that this somewhat contradicts the purpose of PQ, but I have cases where other users copy individual lines from the data, and they want the dynamic function in case they need to test different prices or volumes.

Cheers,

Jamie

Hello, there lots of posts about accessing onedrive files and I can do that fine. I am running into problems with using an xlsx which is shared with me as a data source for PQ. When I access the sharepoint online list it show me all the files except for ones shared with me. I can open and edit the file that's shared with me fine so I assume that its something to do with permissions and that there is something extra I need to do in my PQ. The query I have to get at my sharepoint is:

let

Source = SharePoint.Files("https://XXX-my.sharepoint.com/personal/ZZZ_co_nz/", [ApiVersion = "Auto"])

in

Source

]]>let

Source = SharePoint.Files("https://XXX-my.sharepoint.com/personal/ZZZ_co_nz/", [ApiVersion = "Auto"])

in

Source

Hi,

I'm creating a financial model where a "Product" will be launched several times over the coming years. The Product will have a predictable financial profile but I'm trying to build in flexibility to run scenarios where we specify how many of these products are launched in a given year. I'm trying to calculate the total amount for a given account e.g. Revenues in a year, taking into account the revenue profiles of all the products launched in the previous year.

E.g. If a Product produces Revenues of 100, 200, and 400 in Years 1-3 respectively, and I launch 1 Product in each of the first 3 years, then the Revenues in Year 3 should be 600 (400 from the product launched in Year 1, 200 from the product launched in Year 2, and 100 from the product launched in Year 3).

I have tabular data which includes:

1) Annual Account profiles for each product e.g. Revenues from Year 1 through Year X

2) Profile of the number of products launch in a given year i.e. Product launches in each of Years 1 through X

I've figured out a way to do this with helper rows where I transpose the Product launches row (2) and then multiply it by the account profiles (1). The problem with this is that I'm modeling over many years and this creates an unnecessary amount of these "helper rows" for each account that I model e.g. Revenues, COGS, Opex, etc.

I'm trying to figure out a way to do the above programmatically through Power Query but I'm getting tripped up by the staggered nature of this model.

Below is a link to an Excel example, which is hopefully better than my explanation. Essentially, I've got the cells with blue text in data tables and I'm trying to get the cells highlighted in yellow, without using the cells with red text.

Excel example: https://www.dropbox.com/s/noq4a1tras...odel.xlsx?dl=0

I know this is a mouthful but I'd appreciate any help on this.

Thanks in advance!

Kind regards,

Uwais

]]>I'm creating a financial model where a "Product" will be launched several times over the coming years. The Product will have a predictable financial profile but I'm trying to build in flexibility to run scenarios where we specify how many of these products are launched in a given year. I'm trying to calculate the total amount for a given account e.g. Revenues in a year, taking into account the revenue profiles of all the products launched in the previous year.

E.g. If a Product produces Revenues of 100, 200, and 400 in Years 1-3 respectively, and I launch 1 Product in each of the first 3 years, then the Revenues in Year 3 should be 600 (400 from the product launched in Year 1, 200 from the product launched in Year 2, and 100 from the product launched in Year 3).

I have tabular data which includes:

1) Annual Account profiles for each product e.g. Revenues from Year 1 through Year X

2) Profile of the number of products launch in a given year i.e. Product launches in each of Years 1 through X

I've figured out a way to do this with helper rows where I transpose the Product launches row (2) and then multiply it by the account profiles (1). The problem with this is that I'm modeling over many years and this creates an unnecessary amount of these "helper rows" for each account that I model e.g. Revenues, COGS, Opex, etc.

I'm trying to figure out a way to do the above programmatically through Power Query but I'm getting tripped up by the staggered nature of this model.

Below is a link to an Excel example, which is hopefully better than my explanation. Essentially, I've got the cells with blue text in data tables and I'm trying to get the cells highlighted in yellow, without using the cells with red text.

Excel example: https://www.dropbox.com/s/noq4a1tras...odel.xlsx?dl=0

I know this is a mouthful but I'd appreciate any help on this.

Thanks in advance!

Kind regards,

Uwais

Hi all,

So I am currently working on creating an automated dashboard that updated every time a new file is added to the source folder. I have been able to successfully get excel to update the new data every time a file is loaded.

I have a 'date' column so I can extract, day of the week, year and everything else necessary! *NOTE: I am using POWERQUERY to automate.

The next step is to find a way to automatically calculate and update the following:

Week-to-date: "Sum of Sales for Week #X in Month 2" COMPARED TO "Sum of sales for Week #X in Month 1" (OUTPUT SHOULD BE A %)

Month-to-date: "Sum of Sales for Month#X in Year 2" COMPARED TO "Sum of sales in Month#X in Year 1"

Year-to-date: "Sum of Sales in Year #2" COMPARED TO "Sum of sales in Year#1"

HOW DO I SUCCESSFULLY INPUT ABOVE AS FORMULAS ON POWERQUERY?? PLEASE HELP, I WILL BE FOREVER INDEBTED

]]>So I am currently working on creating an automated dashboard that updated every time a new file is added to the source folder. I have been able to successfully get excel to update the new data every time a file is loaded.

I have a 'date' column so I can extract, day of the week, year and everything else necessary! *NOTE: I am using POWERQUERY to automate.

The next step is to find a way to automatically calculate and update the following:

Week-to-date: "Sum of Sales for Week #X in Month 2" COMPARED TO "Sum of sales for Week #X in Month 1" (OUTPUT SHOULD BE A %)

Month-to-date: "Sum of Sales for Month#X in Year 2" COMPARED TO "Sum of sales in Month#X in Year 1"

Year-to-date: "Sum of Sales in Year #2" COMPARED TO "Sum of sales in Year#1"

HOW DO I SUCCESSFULLY INPUT ABOVE AS FORMULAS ON POWERQUERY?? PLEASE HELP, I WILL BE FOREVER INDEBTED