PQ Nested If, Ceiling, Column.List Questions|Power Query|Excel Forum|My Online Training Hub

New to M Code and I’m stuck! I have a dimension table as shown below. The criteria in each column can change. The PL column can be “Z0” or “Z1” indicating a price level and the Round Up Y or N column, of course, can only be “Y” or “N”. The (+/-) columns are price adjustment columns and can be any % or $ amount. The last column is a roundup to the nearest $ amount entered in that cell based on the Y or N entry. Since both the Z0 and Z1 price columns have nine columns of data, such as shown below in column AA-AI, I want to limit the custom column to one column if possible or keep the additional columns to a minimum.

I have a couple of questions:

1. Is it possible to adapt the following code to accommodate both price levels and each of the price adjustment columns (Z0 and Z1)? Can it be done in the same custom column? The first if statement below works properly as shown in the query example below, but the second line of code returns a null value.

Code thus far:

if [PL.1] = "Z1" and [#"% (+/-)"] <> "" then [Z1 FABRIC] * [#"% (+/-)"] else    //working line of code

if [PL.1] = "Z1" and [#"$ (+/-)"] <> "" then [Z1 FABRIC] + [#"$ (+/-)"] else    // this line of code returns null

[Z1 FABRIC]

Code also needs to work for the other price level – “Z0”

if [PL.1] = "Z0" and [#"% (+/-)"] <> "" then [Z0 FABRIC] * [#"% (+/-)"] else     

if [PL.1] = "Z0" and [#"$ (+/-)"] <> "" then [Z0 FABRIC] + [#"$ (+/-)"] else

[Z0 FABRIC]

Dimension Table

Attached

Query result as mentioned in the code example above.

Attached

2. How can the ceiling formula be used to calculate the roundup based on the Y or N, and the contents of each of the (+/-) columns?

For instance in the Excel formula below, when roundup is used to roundup to the next $5.

Ceiling(sum(Z1 Fabric * % (+/-),5)

Row 2 above example:

795*1.05%=834.75

834.75 = 835 ceiling

3. Can Z1 Column1, Z1 Column2, etc. (future price columns) be dynamically defined in the code with a list from a table to automatically update when the column is assigned a permanent header name? If so, how do I go about this?

I know this is a lot, but I am really stuck on these questions. The first two questions are priority. Question number 3 can wait if necessary. The examples I’ve located online have not been effective.

I hope this makes sense. I would be extremely thankful for any help anyone can provide.