Results 1 to 3 of 3

Thread: Understanding how to make a dynamic range in combination with other M formulas

  1. #1
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Understanding how to make a dynamic range in combination with other M formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Through researching the internet I was able to find a shortcut to change all the columns, except a given number of columns from the left, to the same type thus making it dynamic. For example, if I wanted to change all the columns except the first two to a type number I can enter the following formula: = Table.TransformColumnTypes(#"Previous Step",List.Transform(List.Skip(Table.ColumnNames(#"Previous Step"),2), each{_, type number}))

    I wanted to apply this technique to other operations. Currently I would like to divide all columns except the first two by 100. The table may continually add new columns with new data available so I would like to make M code dynamic. Applying the previous formula I would think this code would work: = Table.TransformColumnTypes(#"Previous Step",List.Transform(List.Skip(Table.ColumnNames(#"Previous Step"),2), each{_ / 100, type number}))
    However it doesn't and I would like to know why and how to fix this error. Is there a universal way to reference all the columns except a select few that I can just plug?

  2. #2
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    25
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    I don't know if the syntax is the same but try Table.TransformColumns instead of Table.TransformColumnTypes.

    Norm

  3. #3
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Thanks for the reply! I think the syntax might be different, I did a plug and replace and it didn't work.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •