question

DavidW-9705 avatar image
0 Votes"
DavidW-9705 asked DavidW-9705 commented

Table.ReplaceValue - add numerical values to exisiting column cell data for multiple columns (without adding new columns)

Hi All,

I'm trying to modify a number of new columns I've merged into an existing table by adding numbers stored in a column of values from within the initial table.
So currently I have all the columns within one table.
I have a column of dates [Date] that I have formatted as numerical to avoid difficulties of 'time' format. I intend to format back to a date column at the end of the query. This is the column of data I want to add into all the new columns.

In the new columns that I have imported are various whole integer numbers; 0,7,-7,14,-14,21,28,42 etc. which signify a number of days before or after the date that is shown in each row of the original [Date] column.
These new columns can be thought of as 'checkpoints' for a project - with the [Date] column having the 'master' date need to calculate from.

So the data looks something like this;

[Date] [CP1] [CP2] [CP3] [CP4] [SCP1] [SCP2]
43034 7 -14 21 -28 0 32
43034 7 -14 21 -28 0 32
43146 7 -14 21 -28 0 32
43271 7 -14 21 -28 0 32

There are many dates and there are many checkpoints, most are positive numbers, but some are negative. Obviously, this is just a mini dummy subset.

However to make things a little easier I hoped, all are full days - so integer whole numbers - they are to be used on a work plan tracker.
I have removed 'null' values and all the checkpoint columns contain the same initial number for the whole column. The [Date] column contains different values for each row, but the required integer offset in the new checkpoint columns is always common for each row.

I don't want to create NEW custom or added columns that sum the [Date] with the relevant checkpoint integer as then I will need to remove/rename many columns. Maybe this could be automated - I found an excellent blog about renaming multiple columns using allist so that would not be too bad, but I couldn't find anything about creating many custom columns at once automatically.

The checkpoint columns could be named anything by the user when they are pilled into the Query and there could be any number of them - so I have to keep my Query without hard-coded reference to these column names if possible.

I want to be able to replace the existing values with the same value PLUS the simple addition of the corresponding [Date] value to form a new date value in the checkpoint columns.
Virtually everything I have found online deals with replacing values by either looking up a specific value or treating values as text and providing examples of wording changes - I have really struggled to find many blogs relating to just mathematical formulas that could be applied in this situation.

My end goal is something like this;

[Date] [CP1] [CP2] [CP3] [CP4] [SCP1] [SCP2]
43034 43041 43020 43055 43062 43034 43066
43034 43041 43020 43055 43062 43034 43066
43146 43153 43132 43167 43174 43146 43178
43271 43278 43257 43292 43299 43271 43303

I have created a Step that refers to the columns I want to change as "ColNames" and I have successfully used this with a hard-coded Table.ReplaceValue to automatically change all values of any specific value I choose to be replaced with the [Date] value but my attempts at writing the syntax and code to add 'any' value with the [Date] are failing.

This line works for a hard-coded value;

= Table.ReplaceValue(#"Filled Up", 14, each [Date],Replacer.ReplaceValue, ColNames)

where #'Filled Up' is the prior lien of code (source I assume).

What doesn't work - but does not create any errors either (just nothing seems to happen) is;
= Table.ReplaceValue(#"Filled Up", ColNames, each ColNames + [Date],Replacer.ReplaceValue, ColNames)

Where ColNames is a list of columns created earlier in the Query (using List.RemoveMatchingItems........) that is meant to indicate all the Checkpoint columns I want to apply the replacement values against.
It seems to work in the first line of the hard-coded step I have shown above as any column in my list with a value of 14 is changed to have the corresponding correct [Date] value for that row correctly shown.
So the List works in the last part of the equation and interestingly it does not give the syntax of the steps in the Power Query Editor an issue when used to replace the hard-coded 14 and in an addition, formula to add to the [Date], as far as the Query logic goes, but as mentioned the line produces no changes whatsoever.

If anyone has any recommendations I'd really appreciate some pointers.

Many thanks in advance for your time!

Dave



power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MiguelEscobar-3907 avatar image
0 Votes"
MiguelEscobar-3907 answered DavidW-9705 commented

The way that the Table.TransformColumns and the Table.ReplaceValue functions work is that it only gives you access to the value inside the column that you're trying to transform. They're not really meant to do the type of transformations that you're looking for on a row by row basis - if it as a fixed value it would be super easy to do with either one, but trying to do it as dynamically as you require is something that neither of these functions are meant to do at the time.

I'd still encourage you to try doing it on a step by step, but if you absolutely need to take the code route, here's an example of how to accomplish that:

 let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2MDZR0lEyBGIjIDZWitUBiRqZg0R0wQRIXBcmYWhiBuSCtJgCsZlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, A = _t, B = _t, C = _t]),
   #"Changed column type" = Table.TransformColumnTypes(Source, {
                 {"Date", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
   Custom = Table.FromRecords( Table.TransformRows(#"Changed column type", (r)=> [A= r[A] + r[Date], B= r[B] + r[Date], C= r[C] + r[Date] ]) ),
   #"Changed column type 1" = Table.TransformColumnTypes(Custom, {
                 {"A", type date}, {"B", type date}, {"C", type date}})
 in
   #"Changed column type 1"
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Miguel,

I 'm not sure why the functions are limited in their use to suiting fixed values more necessarily - they do cope with a more dynamic approach as you have shown.

Your solution certainly works thanks!

0 Votes 0 ·