Power Query columns depending on value

[naveen73]

Hi all,

I have a row of values. See below screenshot (I am not able to attach the Excel file 🤔).

In each of the columns to the right I want the total of each value that is 70 at maximum if the whole of the value is used. If the whole of the value will be higher than 70, then take the part of the value that reaches the value of 50.

I want to do this in Power Query as the column of values changes every month. So the number of columns to the right will change too. The number of column will stop when the last value is used.

Thanks,

Naveen

[spinfuzer · Accepted solution]

Deleting old replies and pasting newest code here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjNXitWJVjIxBVOmZmDKHEIZm4ApQyMIbWKOLGkG1BELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    dif_cols = Table.Combine(let
            limit1 = 120, // change upper limit 
            limit2 = 100, // chagne lower limit
            min = limit2, //List.Max({70,limit2}), old code for testing
            buffer = List.Buffer(#"Changed Type"[Value]), // Change to TableName[ColumnName]
            ct = List.Count(buffer)
        in 
            List.Generate( () =>
            [ 
                n = 0, 
                curr = {buffer{n}}, 
                total = curr{0}, 
                remainder = 0,
                col = 1
            ], 
            each [n] < ct,
            each [ 
                n = [n] + 1, 
                curr = if [total] <= min then { buffer{n} } & [curr] else {buffer{n}} , 
                total = List.Sum(curr) + [remainder],
                remainder = if total >= limit1 then List.Max({total - limit2,0}) else if total >= min then 0 else [remainder],
                col = if [total] <= min then [col] else [col] + 1
            ],
            each 
                #table(
                    {//"test", 
                        "Col " & Text.From([col]),"Col " & Text.From([col]+1)}, // Change "Col " to change prefix
                        {
                            //{[total]} & 
                            (if [total] >= limit1 then {[curr]{0}-[remainder],[remainder]} else {[curr]{0},null})
                        }
                )
            )),
    add_cols = Table.FromColumns(
        Table.ToColumns(#"Changed Type") & Table.ToColumns(dif_cols), 
        Table.ColumnNames(#"Changed Type") & Table.ColumnNames(dif_cols)
    ) // Change to #Changed Type to your prior table name
in
    add_cols

[spinfuzer]

See latest response

[naveen73]

Hi @spinfuzer  ,

Thanks for this.

1. I probably should have mentioned this earlier but when the total is higher than 70 code subtract. No subtraction should take place regardless of the amount.
2. At first glance it seems to work but when I change the number 70 to 90 and 50 to 80, the result can be seen in the screenshot 1.

However, I expected cell B4 to be 17, cell C4 to be 24 and cell C6 21 as in screenshot 2

I get my data from another query in Power Query so should I change the following?

“Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFQitWJVjIyBlMmhmDK2BSFZwbhQZQYG6HImSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]), “

Thanks so much!!!. It is much appreciated!

Naveen

[spinfuzer]

The Source is just a manually entered table.  You can click on the Cog on the right of the source step in the applied steps to manually edit the table values.

see latest reply for updated code

[naveen73]

Hi @spinfuzer ,

Thanks so much for both replies. 

I can see two codes here. Not sure which one to use. But both of them cap the sum of each column to 80.

Maybe it will be helpful if I explain it like this:

• When the sum is higher for example 120 then remove the last amount.
• When removing the last amount the total is between 100 and 120, take that total.
• When removing the last amount brings the total to lower than 100, then add a value that brings the total to 100. The remainder will be used for the next day/column.
• When the amount is >120 then cap it 120 and use the remainder for the next column

The limits (120, 100 etc. etc.) I want to use parameters that will change from an Excel cell. I can see why there was confusion previously. Sorry about that.

See a link with a file. I have granted access to it already:

https://docs.google.com/spreadsheets/d/1K0o0X4P7ztJSGpfcXlXKpS06p9tiYqSh/edit?usp=sharing&ouid=11029...

Thanks,

Naveen

[spinfuzer · Accepted solution]

Deleting old replies and pasting newest code here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjNXitWJVjIxBVOmZmDKHEIZm4ApQyMIbWKOLGkG1BELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    dif_cols = Table.Combine(let
            limit1 = 120, // change upper limit 
            limit2 = 100, // chagne lower limit
            min = limit2, //List.Max({70,limit2}), old code for testing
            buffer = List.Buffer(#"Changed Type"[Value]), // Change to TableName[ColumnName]
            ct = List.Count(buffer)
        in 
            List.Generate( () =>
            [ 
                n = 0, 
                curr = {buffer{n}}, 
                total = curr{0}, 
                remainder = 0,
                col = 1
            ], 
            each [n] < ct,
            each [ 
                n = [n] + 1, 
                curr = if [total] <= min then { buffer{n} } & [curr] else {buffer{n}} , 
                total = List.Sum(curr) + [remainder],
                remainder = if total >= limit1 then List.Max({total - limit2,0}) else if total >= min then 0 else [remainder],
                col = if [total] <= min then [col] else [col] + 1
            ],
            each 
                #table(
                    {//"test", 
                        "Col " & Text.From([col]),"Col " & Text.From([col]+1)}, // Change "Col " to change prefix
                        {
                            //{[total]} & 
                            (if [total] >= limit1 then {[curr]{0}-[remainder],[remainder]} else {[curr]{0},null})
                        }
                )
            )),
    add_cols = Table.FromColumns(
        Table.ToColumns(#"Changed Type") & Table.ToColumns(dif_cols), 
        Table.ColumnNames(#"Changed Type") & Table.ColumnNames(dif_cols)
    ) // Change to #Changed Type to your prior table name
in
    add_cols

[naveen73 · Accepted solution]

@spinfuzer

Thanks so much for this solution. It does what I asked for. Much appreciated!!

[naveen73]

Hi,

Thanks so much for this. It is really helpful and is working as I hoped. 👍

The next step would be to have headers of the columns being a date.

There will be an extra column on the left. (I have out it in the data)

At some point the date in the most left column will be the same as the header of a newly create column. In that case the amount should be stated in full regardless of the rules above.

The date for the first would come from cell C4.

Would I have to make a new thread for this addition?

Naveen

[v-junyant-msft]

Hi @naveen73 ,

Generally a post only solves one problem, your original problem has been solved. Any effort deserves to be honored, please mark @spinfuzer 's answer as a solution, thank you!
If you have any other follow-up questions, please start a new thread! Thank you for your understanding and I apologize for any inconvenience!

Best Regards,
Dino Tao

[Fowmy]

@naveen73 

Are you refering to the solution from @spinfuzer ?

[naveen73]

Apologies, yes, I meant to respond to @spinfuzer .

[naveen73]

Hi,

Please see the link of the file. All is demo data anyway but in the spreadsheet the text is more readable.

Also, when providing the answers, could you add some links that I use for future reference when trying to sovle similar problems?

https://docs.google.com/spreadsheets/d/1u5HS0RsYOy32RXl_WvMCtdbF8o8mN5C8/edit?usp=drive_link&ouid=11...

Thanks again,

Naveen

Naveen

[Fowmy]

@naveen73 

Please grant access to view the file shared

[naveen73]

Sorry about this. Just gave permission.

[Fowmy]

@naveen73 

Can you share some sample data with the desired output to have a clear understanding of your question?

You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.