Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LeroyPaul
Helper I
Helper I

Dynamic value replacement based on column type

Hello,

 

I have been stuck with this problem for several days. Here is the problem:

 

I have data columns with numbers except some are with "," and some with "."
I would like to dynamically convert the columns containing "." to ","

 

Here is an example : 

 

XXX PriceYYY PriceXXX QuantityYYY QuantityProduct
11,5512.4013.5012,50AAAAA
18,9015.9074.6065,15BBBBBB
21,5025.9054.9061,10CCCCC

 

What I want is that all the columns that contain "Price" and "Quantity" are changed to type Number by replacing the "." by ","

Knowing that it must be dynamic because I have columns "ZZZ Price" and "ZZZ Quantity" that can appear.

 

I tried this solution:

 

= Table.TransformColumns(Source, List.Transform(List.Select(Table.ColumnNames(Source),each Text.Contains(_,"Price") or Text.Contains(_,"Quantity")), (col) => {col, each _ ?? null, type text}))

 

This code converts everything to text which allows me to change the "." to ","
But I have an error on the columns that don't contain "." because the type of these columns don't seem to be changed to text

 

Thanks for your help

Paul

1 ACCEPTED SOLUTION
LeroyPaul
Helper I
Helper I

Thank you for your answers, they helped me find the solution.

 

The problem comes from my formula that allows me to dynamically change the type of columns that contain the words "Price" and "Quantity".

 

So I used the following formula to change the type of the columns to text :

 

= Table.TransformColumnTypes(Source, List.Transform(List.Select(Table.ColumnNames(Source),each Text.Contains(_,"Price") or Text.Contains(_,"Quantity")), each {_, type text}))

 

This allowed me to use the following formula to replace the "." by "," without error :

 

= Table.ReplaceValue(PreviousStep,".",",",Replacer.ReplaceText,List.Select(Table.ColumnNames(PreviousStep),each Text.Contains(_,"Price") or Text.Contains(_,"Quantity")))

 

 

View solution in original post

5 REPLIES 5
LeroyPaul
Helper I
Helper I

Thank you for your answers, they helped me find the solution.

 

The problem comes from my formula that allows me to dynamically change the type of columns that contain the words "Price" and "Quantity".

 

So I used the following formula to change the type of the columns to text :

 

= Table.TransformColumnTypes(Source, List.Transform(List.Select(Table.ColumnNames(Source),each Text.Contains(_,"Price") or Text.Contains(_,"Quantity")), each {_, type text}))

 

This allowed me to use the following formula to replace the "." by "," without error :

 

= Table.ReplaceValue(PreviousStep,".",",",Replacer.ReplaceText,List.Select(Table.ColumnNames(PreviousStep),each Text.Contains(_,"Price") or Text.Contains(_,"Quantity")))

 

 

v-stephen-msft
Community Support
Community Support

Hi @LeroyPaul ,

 

You want to replace the "." in multiple columns with ",", right? 

If so, first make sure the columns contains "." are text type.

vstephenmsft_0-1675665408685.png

You can remove the "Change Types1" from Applied Steps to make them text.

vstephenmsft_5-1675665860991.png

Or select them and change types manually to make them text.

vstephenmsft_6-1675665922743.png

 

After the text type modification is complete, select columns at the same time, and then right-click a column to select the "Replace Values...".

vstephenmsft_2-1675665733584.png

vstephenmsft_3-1675665748709.png

vstephenmsft_4-1675665755373.png

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thank you for your answer.
Your solution allows me to do the operation in a static way but does not take into account the name of the columns so that it is dynamic.
As I will have new columns that will be added to my model later, these columns will not be modified using this solution.
Thanks for your explanations and for the detail of your solution

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @LeroyPaul  - you should consider using the Replace in Multiple Columns - Gorilla BI in two steps rather than Transform Columns in one step.  Use the Table.ColumnNames function to find Price and Quantity in these separate applied steps.

Thank you for your answer,
I was able to develop my thoughts with the help of this website.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors