cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kgiboin
Frequent Visitor

Transforming Multiple Columns without adding new columns in Power Query

Hi,

I have been able to achieve the first part like so:

kgiboin_0-1642609270088.png

by inserting this code:

= Table.ReplaceValue(#"Source", each [SUP1], each [SUP1]*[Operator], Replacer.ReplaceValue, {"SUP1"})

I am able to change the value of SUP1 as below:

kgiboin_1-1642609365756.png

 

However I want to change the values of a range of columns, not just the SUP1 column. 

How do I change the replace value code to affect all the Columns in the table e.g SUP2 SUP3 SUP4... 

Thanks!

 

 

 

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @kgiboin ,

 

In your current data format, you would not be able to apply this to multiple columns at the same time - you would need to create a new replace step for each [SUP2], [SUP3] etc.

 

However, you should really unpivot your SUP columns (multi-select [PART NUMBER] and [Operator] then Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns).

This will firstly allow you to perform your multiplication replacement step generically on a single column ([Value]), but also restructure your data into the most efficient format for reporting.

 

Pete

View solution in original post

Unpivoting is definitely the best way to go but it is possible to do multiple columns simultaneously even without explicitly referencing each one if you do some fancy row transformations.

 

Table.FromRecords(
    Table.TransformRows(
        #"Previous Step Name",
        (row) => Record.TransformFields(
                     row,
                     List.Transform(
                         List.Skip(Record.FieldNames(row), 2),
                         (name) => {name, each Record.Field(row, name) * row[Operator]}
                     )
                 )
    )
)

 

 

View solution in original post

3 REPLIES 3
kgiboin
Frequent Visitor

I found another way to do it without unpivoting:

 

= Table.FromRecords(Table.TransformRows(#"previous step name",(row) => Record.TransformFields(row,List.Transform(List.Skip(Record.FieldNames(row), [enter the nth column you want the formula to start]),(name) => {name, each Record.Field(row, name) * row[#"Operator"]}))))

BA_Pete
Super User
Super User

Hi @kgiboin ,

 

In your current data format, you would not be able to apply this to multiple columns at the same time - you would need to create a new replace step for each [SUP2], [SUP3] etc.

 

However, you should really unpivot your SUP columns (multi-select [PART NUMBER] and [Operator] then Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns).

This will firstly allow you to perform your multiplication replacement step generically on a single column ([Value]), but also restructure your data into the most efficient format for reporting.

 

Pete

Unpivoting is definitely the best way to go but it is possible to do multiple columns simultaneously even without explicitly referencing each one if you do some fancy row transformations.

 

Table.FromRecords(
    Table.TransformRows(
        #"Previous Step Name",
        (row) => Record.TransformFields(
                     row,
                     List.Transform(
                         List.Skip(Record.FieldNames(row), 2),
                         (name) => {name, each Record.Field(row, name) * row[Operator]}
                     )
                 )
    )
)

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!