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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Will_Ryu
Frequent Visitor

Add an character in front of each value in a list

I have a follow up request to my earlier query 

 

If I need an apostrophe in front of each value, how would I do that

 

I tried  - " '" & Text.Combine(Source [Name] ,"' ,") but the output I get only has an apostrophe for the first value like so 'Will', Sam', Jo',  

 

What I need is - 'Will', 'Sam', 'Jo', - an apostrophe in front of each  value

 

I can convert the list to a table, add the delimiters and then convert back to a list

 

Is there a more elegant way to do this?

 

Thanks

9 REPLIES 9
asethi
Regular Visitor

Hi @Will_Ryu 

Could you find a solution to your problem? If yes, can you please share it.

Thanks

Please try this

 

let
Source = Text.Combine(List.Distinct (Table[Field]) ,",")
in
Source

 

This will give you the list and you can then use it as needed

 

Let us know if it works

Hi @asethi 

Can you please provide more details about your requirement? Are you looking for a dax or M solution?

Hi @tamerj1 

 

I am looking for passing a excel table with text values to Text.Combine. Actually, my problem is exact same as what Will has mentioned above.

 

Thanks

@asethi 

I'm not asking to expose your data. Only provide one or two rows sample dummy data that simulates your original data along with expected results. Example

Source table column:

Name

Same,Jo,Roger,Tara

X,Y,Z

Happy,New,Old,Text

 

Expexted Output

Name New

'Same','Jo','Roger','Tara'

'X','Y','Z'

'Happy','New','Old','Text'

 

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Transform(Text.Split([Name],","), each "'"&_&"'"),","))
in
    #"Added Custom"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thanks. @asethi  might find it useful

tamerj1
Super User
Super User

Hi @Will_Ryu 

is dax an option for you? If yes please provide a sample of 4 - 5 rows showing input and exprcted output. 

Hi tamerj1 Thanks for responding

 

I am not using DAX and would like to use M for this purpose

 

I understand that I could do it this way:

 

let
Source = #table({"Name"}, {{"Will"}, {"Sam"}, {"Jo"}, {"Roger"}, {"Tara"}}),
#"Added Custom" = Table.AddColumn(Source, "CST", each "'" &[Name] & "'"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name"}),
output = Text.Combine(List.Distinct (#"Removed Columns" [CST]) ,",")
in
   output

 

However, I was looking for a more elegant solution.

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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