Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Dynamic transformation and rounding

Hello all,

 

I need to change the type only on the columns with  $ in the header.

 

I have the following code:

 

TransformDollars = Table.TransformColumnTypes(
renameColumns,
List.Transform(
List.Select(Table.ColumnNames(renameColumns), each Text.Contains(_, "$")), each {_, Number.Type})
),

Dollars_Columns_Selected = List.Select(Table.ColumnNames(TransformDollars), each Text.Contains(_, "$")),

RoundDollars = Table.TransformColumns (
TransformDollars,
List.Transform (
Dollars_Columns_Selected,
each {_, Number.Round(_, 3), type number}))

in RoundDollars

 

but I get this error:

 

Thanks for your help.

 

Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
        RoundDollars = Table.TransformColumns (
        TransformDollars,
        List.Transform (
            Dollars_Columns_Selected,
//         each {_, Number.Round(_, 3), type number})) >> this is wrong, must be 
            each {_, (x) => Number.Round(x, 3), type number}
        )
    )

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    sounds like a mixup between column names and column values.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • AlienSx's avatar
      AlienSx
      Super User
      1 year ago
          RoundDollars = Table.TransformColumns (
        TransformDollars,
        List.Transform (
            Dollars_Columns_Selected,
//         each {_, Number.Round(_, 3), type number})) >> this is wrong, must be 
            each {_, (x) => Number.Round(x, 3), type number}
        )
    )
  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    use the following formula, source is the name of table in the previous step

     

    = List.Accumulate(List.Select(Table.ColumnNames(Source), each Text.Contains(_,"$")),Source,
    (a,b)=>Table.TransformColumns(a,{{b, each Number.RoundUp(_,3), Int64.Type}}))

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    Hello,

    did you mean changes like this:

     

    RoundDollars = Table.TransformColumns (
    TransformDollars,
    List.Accumulate(
    Dollars_Columns_Selected,
    TransformDollars,
    (a,b)=> Table.TransformColumns(a,{{b, each Number.RoundUp(_,3), Int64.Type}})))

     

    If so, I got the following error:

     

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      1 year ago

      Hi Mic1979 ,

       

      Yes,  Based on the data you provided, I have used it as sample data on my end and successfully implemented it. Therefore, please refer to the attached pbix file.

       

      I hope this should resolve your issue, if you need any further assistance, feel free to reach out.

       

      If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

      Thankyou,
      Have a great day ahead.

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    Hello,

    I don't see any function in your file.

    I know this is possible with standard approach from Power Query, but I am trying to built a function...

     

    Thanks.