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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors