Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional formatting of a table based on header names

Hi

Does anyone have a bit of code that allows me to do something like this

if select table column header contains text "date" changed type "date".

Would be handy to stick this on the end of some of my queries rather than having to go through and select.

Regards

Need Help
Tips and Tricks
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Think Anonymous might mean something like this instead.

    let
    [...],
    previousStepName = [...],
    dateCols = List.Select(Table.ColumnNames(previousStepName), each Text.Contains(Text.Lower(_), "date")),
    transformationsList = List.Transform(dateCols, each {_, type date}),
    autoDateType = Table.TransformColumnTypes(previousStepName, transformationsList)
in
    autoDateType

     

    Or in one step:

    autoDateType =
    Table.TransformColumnTypes(
        previousStepName,
        List.Transform(
            List.Select(
                Table.ColumnNames(previousStepName),
                each Text.Contains(Text.Lower(_), "date")
            ),
        each {_, type date}
        )
     )

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    I'm assuming you mean you want to be able to automatically perform functions on certain columns if they exist in the table.

     

    Try this:

    autoChangeTypes =
    if List.Contains(Table.ColumnNames(previousStepName), "columnToFindName")
    then Table.TransformColumnTypes(previousStepName,{{"columnToFindName", type text}})
    else previousStepName

     

    Pete

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      Think Anonymous might mean something like this instead.

      let
    [...],
    previousStepName = [...],
    dateCols = List.Select(Table.ColumnNames(previousStepName), each Text.Contains(Text.Lower(_), "date")),
    transformationsList = List.Transform(dateCols, each {_, type date}),
    autoDateType = Table.TransformColumnTypes(previousStepName, transformationsList)
in
    autoDateType

       

      Or in one step:

      autoDateType =
    Table.TransformColumnTypes(
        previousStepName,
        List.Transform(
            List.Select(
                Table.ColumnNames(previousStepName),
                each Text.Contains(Text.Lower(_), "date")
            ),
        each {_, type date}
        )
     )