Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ryanb11
Helper III
Helper III

custom column syntax

I have the below custom column in the query editor, where the source is a table with text data and then 2 columns are created upon this.
 
I need to change the source to the table 'rel' and the column 'Score'. No matter what youtube tutorials or format changes i try i keep getting errors.
 
Would some one be able to help amend the syntax to use the source table and column above where applicable?
 
let
  Source = #table(
      {"Column1"},
      List.Zip(
          {{
              "Call me on 08588812885",
              "Call me on 07525812845",
              "I need assitance please call me",
              "Last tried in 2019"
            }}
        )
    ),
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Column1], " ")),
  #"Added Custom1" = Table.AddColumn(
      #"Added Custom",
      "Custom.1",
      each Text.Combine(
          List.Select(
              [Custom],
              (l) => [
                        ListOfCharacters = Text.ToList(l),
                        Result = not (List.Count(ListOfCharacters) >= 10
                                    and List.AllTrue(
                                            List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
                                    )
                    )][Result]
            ), " ")
    )
in
  #"Added Custom1"
 

4 REPLIES 4
Jimmy801
Community Champion
Community Champion

Hello @ryanb11 

 

so basically you only need to change the code to a new source`?

Then try this

let
  Source = rel,
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Score], " ")),
  #"Added Custom1" = Table.AddColumn(
      #"Added Custom",
      "Custom.1",
      each Text.Combine(
          List.Select(
              [Score],
              (l) => [
                        ListOfCharacters = Text.ToList(l),
                        Result = not (List.Count(ListOfCharacters) >= 10
                                    and List.AllTrue(
                                            List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
                                    )
                    )][Result]
            ), " ")
    )
in
  #"Added Custom1"

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

i Jimmy, I need to create a new column to an existing table already loaded into Power BI. I dont want to create a table with the amended text values either, would you be able to help with the code to create a new column in the existing 'rel' table. @Jimmy801 

Jimmy801
Community Champion
Community Champion

hello @ryanb11 

 

I cannot follow you. You can use the GUI and choose Add Column --> Custom Column. Insert the text or the formula you want and thats it. In case you don't want to touch your original table, right click on your query/table and select "Reference".

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

lbendlin
Super User
Super User

There's no real need for List.Zip.

 

Create the table like so:

 

let
  Source = #table(
      {"Column1"},
      {{"Call me on 08588812885"},
        {"Call me on 07525812845"},
        {"I need assitance please call me"},
        {"Last tried in 2019"}
       }
   ),

 

Which also shows you how to replace this with your desired source.

 

let
  Source = #table(
      {"Score"},
      {rel[Score]}
            ),

 

But this still looks weird.  Why not use

 

Let Source =  Table.FromList(rel[Score], Splitter.SplitByNothing(), null, null, ExtraValues.Error)

 

?

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors