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
Imad-R
Frequent Visitor

Replace value for several column depend condition power query

Hello,

 

The following will be an example of my problem. 

Here are the data : 

ImadR_0-1669665470367.png

 

I want to filter these data by type of stock so I unpivot the column related to the stock number, and we obtain this table :

ImadR_1-1669665946687.png

 

Only values different to 0 are interesting in the "Valeur" column consequently that's the result :

ImadR_2-1669666180231.png

My problem it's the following, I want to replace some value by 0 depending condition.

For exemple let's focus on the second article:

ImadR_3-1669666832918.png

 

I want to replace cupboard 1 stock value by 0 when modality in "Attribut" column equal "Freezer stock" for the first line, and replace Freezer stock value by 0 when modality = "cupboard 1 stock" for the second line. I want there to be only the value for the stock concerned and not another value for another type of stock that is different to the type of stock entered on the line. And that's for all line and all column concern value of stock. 

 

I have try to write with a M request but it doesn't work. 

 

Someone can help me please !!!

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

Hi @Imad-R,

 

See if the code below works as you want. You can then modified to suite your case:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MTlXSUTIEYrei1NQqpVgdFEHXnOJUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Attribute = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"Attribute", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Attribute], null, (x, y, z)=> if y= "Freez" then 0 else x,{"Value"})
in
    #"Replaced Value"

 

Cheers,

John

View solution in original post

2 REPLIES 2
jbwtp
Memorable Member
Memorable Member

Hi @Imad-R,

 

See if the code below works as you want. You can then modified to suite your case:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MTlXSUTIEYrei1NQqpVgdFEHXnOJUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Attribute = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"Attribute", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Attribute], null, (x, y, z)=> if y= "Freez" then 0 else x,{"Value"})
in
    #"Replaced Value"

 

Cheers,

John

Imad-R
Frequent Visitor

Hello,

 

Thanks, it's working. It is not very convenient because I must make 3 different M requests for each modality of Attribut column but it suits me.

 

Thanks for your reactivity !

 

-- 1
= Table.ReplaceValue(#"Lignes filtrées",each [Attribut], null, (x, y, z)=> if y = "Freezer stock" then 0 else x,{"cupboard 1 stock value", "cupboard 2 stock value"})

-- 2
= Table.ReplaceValue(#"Personnalisé1",each [Attribut], null, (x, y, z)=> if y = "cupboard 1 stock" then 0 else x,{"Freezer stock value", "cupboard 2 stock value"})
-- 3
= Table.ReplaceValue(#"Personnalisé2",each [Attribut], null, (x, y, z)=> if y = "cupboard 2 stock" then 0 else x,{"Freezer stock value", "cupboard 1 stock value"})

 

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