Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello,
I have different files containing various attributes (one file will have Job Name, another one will have Location, another one will have Grade, etc.).
Sample Data:
Job Name |
Controller |
Director |
Analyst |
VP Of Finance |
Director |
I want to have a function that will add a column in which each cell will have combined: the Column Name (Job Name in case of the above sample) AND length of the Column Name (Length of each Job Name in case of the above sample).
Job Name Desired Output
Controller | Job Name 10 |
Director | Job Name 8 |
Analyst | Job Name 7 |
VP Of Finance | Job Name 13 |
Director | Job Name 8 |
What I have is:
(MyColumn) as text =>
let
Source = Text.Length(MyColumn),
NewSource = Number.ToText(Source),
FinalText = Text.Combine({MyColumn,NewSource}," ")
in FinalText
I need to find a way to reference the column name to be added to the FinalText, because for now the output is following:
Job Name Current Output (incorrect!)
Engineer | Engineer 1 |
Director | Director 2 |
Analyst | Analyst 3 |
Engineer | Engineer 1 |
Btw I tried using Record.Field(_, MyColumn) but it didn't work with below error:
An error occurred in the ‘’ query. Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.
Thank you in advance for help.
@AlexisOlson Thank you for your help.
It all looks good, but one thing I would like to change. Is there a way to avoid handwriting "Job Name"?
In my example (below), I am already passing the column name as a Parameter. Can we somehow use this variable to be used automatically in the output (FinalText)?
As an example, If my parameter (MyColumn) is Job Name I want the first argument of Text.Combine to be referenced as JobName, but in the below function it is taking the cell (row) value, instead of the MyColumn.
(MyColumn) as text =>
let
Source = Text.Length(MyColumn),
NewSource = Number.ToText(Source),
FinalText = Text.Combine({MyColumn,NewSource}," ")
in FinalText
I don't quite follow. You need to specify "Job Name" once so the function knows what column you want to read text lengths from. There's no other place in what I suggested that has "Job Name" hard coded and the output is what you specified.
Maybe if I rewrite it a bit more like your code it will be slightly clearer?
(R as record, MyColumn as text) as text => //R = row 1, MyColumn = "Job Name"
let
Text = Record.Field(R, MyColumn), //Returns R[Job Name] = "Controller"
Source = Text.Length(Text), //Returns length("Controller") = 10
NewSource = Number.ToText(Source), //Returns "10"
FinalText = Text.Combine({MyColumn, NewSource}, " ") // "Job Name" & " " & "10"
in
FinalText // "Job Name 10"
Sorry for the late reply.
In this function, I have two parameters: R & MyColumn. MyColumn works as a drop-down so I can pick a column from the drop-down list, but R is a parameter that I have to hand-write. See below:
Unfortunately, I can't think of any way to make this work using the Invoke Custom Function GUI and zero manual typing but typing fn_Len(_, "Job Name") in the Custom Column GUI isn't that bad (use Query1 instead of fn_Len if that's what your function is named).
This doesn't work well with just a single input parameter. Try writing where you pass in the record too.
(R as record, MyColumn as text) as text =>
let
Text = Record.Field(R, MyColumn),
TextLen = Number.ToText(Text.Length(Text)),
FinalText = MyColumn & " " & TextLen
in
FinalText
If the name of the function is fn_Len, then you can invoke it as a new custom column like this:
fn_Len(_, "Job Name")
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.