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

power bi 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"
 
1 ACCEPTED SOLUTION

Hi @ryanb11 ,

Please check if the below screen shot is what you want? If yes, please update it in Power Query Editor.

1. Choose the related query and right click to choose "Rename" option, rename the query name as "rel"

2. Navigate to Home ribbon, click "Advanced Editor". Copy and paste the below codes into the opening window:

let
  Source = #table(
      {"Score"},
      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([Score], " ")),
  #"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"

power bi custom column syntax.JPGBest Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
amitjzaveri
Resolver II
Resolver II

For trial purposes, I copied the example data in Excel and used that source in the following transformation and it worked fine.

What is the new source and what error are you getting?

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\amitz\Desktop\rel.xlsx"), null, true),
    rel_Sheet = Source{[Item="rel",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(rel_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Score", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Split([Score], " ")),
  #"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"

 

 

@amitjzaveriexcel is not the source i am using so think it is better if you load in the data to power bi and then write the custom column logic.

 

My source is a table already loaded into power bi, so it isbetter to not use the custom column logic to reference excel.

 

If you can kindly help with this it would be appreciared,

@ryanb11  It seems you're trying to use a source from a separate table in the same model and it is not getting referred. If that is the case then you will have to duplicate that table and write follow up transformation lines. 

 

Otherwise if you can share some screenshot then it would help to clear up things.

 

Did I answer your question? Mark my post as a solution!

I have told you the source table so whty are you unable to help me with the syntax?

 

I cannot send screenshots of sensitive data.

 

Please can you help with the syntax from the 'rel' table I have in power bi.

 

 

Hi @ryanb11 ,

Please check if the below screen shot is what you want? If yes, please update it in Power Query Editor.

1. Choose the related query and right click to choose "Rename" option, rename the query name as "rel"

2. Navigate to Home ribbon, click "Advanced Editor". Copy and paste the below codes into the opening window:

let
  Source = #table(
      {"Score"},
      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([Score], " ")),
  #"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"

power bi custom column syntax.JPGBest Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

error messages include it does not recognise the table i am trying to use as a source. 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors