Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Transform a specific value in a column

if the value of a certain column is in the same line or it's correspondent to a certain term on another column, how can that specific value be transformed or formated as percentile on power bi?   f...
Need Help
  • edhans's avatar
    edhans
    2 years ago

    I think you will need to use the Table.TransformRows function here. See this code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU01VNJRMtQzNlGK1YGKGAFFjPRMzBEixmA1ZqYIEROwGlMjhIgpWI0JkogZWMTUAiFiDtZljmSXBVjEGGhyLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Topic = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type number}}),
    ConditionalTransform = 
        Table.FromRecords(
            Table.TransformRows(
                #"Changed Type",
                (x) => Record.TransformFields(
                    x, {"Values", each if x[Topic] = "Value4" then x[Values] /100 else x[Values]}
                )
            )
        )
in
    ConditionalTransform

    I am just dividing any record with Value4 in the Topic column by 100, otherwise I am not touching it.

     becomes this:

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi! Thank you so much for answering! Does this solution create another table or it can edit in the table itself? The table in the question is a simple example, i have a much more complex table that has many more columns, with this code, it only takes the two columns it refers to and creates another table. Is there a solution to this? thx

edhans's avatar
edhans
Community Champion
2 years ago

This creates a temporary table in memory to do the swap out, but replaces in the original table. I am not sure what your actual code is, but it should be adaptable to this. This creates a table of the entire record for the current row and does the if/then/else logic. It doesn't matter how many fields in the record, 2, 20, or 200.