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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ottiphi
Helper I
Helper I

Expression.Error: We cannot convert a value of type List to type Text.

Hello all,

 

Please see the Scrrenshot. I have a column named "Hashtag". Each cell is automatically getting data from sharepoint. the user has in sharepoint the option to tick Checkboxes (allow multiple selections). I now need the cells to be text cells. How can I transform the List within each cell into text (hashtags devided with ","  ?)

 

Thank you so much for your help!

 

Unbenannt.PNG

7 REPLIES 7
Greg_Deckler
Super User
Super User

Use Text.Combine:

 

https://msdn.microsoft.com/en-us/library/mt253358.aspx



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hello. I already had that idea. Can you specify a little bit more and maybe write a lign of code that I could use? I am not a coder! Also, where exactly should I insert the function?

You should be able to put it whereever in your query, open up Advanced Editor and add a line. Here is an example function I wrote that you can take a look at:

 

let
    fnCombine = () =>
 
let
 
values = {
 
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"A",
"B",
"C",
"D",
"E",
"F"

},

Result = Text.Combine(values,",")

in

Result

in

fnCombine

Also, here it is as a generic function that you should be able to use. Just add a Blank Query, paste in the code below, save function. Add Column -> Invoke Custom Function and send it your column that is a list as its parameter.

 

let
    fnCombine = (values as list) =>
 
let

Result = Text.Combine(values,",")

in

Result

in

fnCombine

 

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hello,

 

Thank you for your detailed explaination. I tried what you told me and used the generic function. Unfortunately it didn't work. Here is the code of the query I hava so far. Can I add a line within this query, that transforms the list within the cells into text?Probably before the changed type line, since after that line, there is not written List within the cells of the columns "hashtag" and "category" anymore, but Error. I would prefer not to open up a new query.

 

let
Source = SharePoint.Tables("https://hilti.sharepoint.com/sites/de001101", [ApiVersion = 15]),
#"3860ef3e-aef8-4ee7-bc63-1c29b67a7e9f" = Source{[Id="3860ef3e-aef8-4ee7-bc63-1c29b67a7e9f"]}[Items],
#"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Idea Title", type text}, {"Date of Idea Entry", type date}, {"Submitter", type text}, {"Hashtag", type text}, {"Mealsize", type text}, {"Idea Status", type text}, {"Ranking of Idea", type text}, {"Meal Size", type text}, {"Implementierung", type text}, {"Category", type text}, {"Hashtag", type text}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"

Also, I will create two new queries afterwards, to reorganize the columns "hashtag" and "category". One example for hashtag:

 

let
    Source = IdeaList,
    #"Select Columns" = Table.SelectColumns(Source,{"Idea Title", "Hashtag"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Select Columns", {{"Hashtag", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Hashtag"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Hashtag", Text.Trim, type text}})
in
    #"Trimmed Text"

I did not write that code...

@ottiphi,

Add a "Extract values" step before change type and check if you get expected result.

 #"Extracted Values" = Table.TransformColumns(#"3860ef3e-aef8-4ee7-bc63-1c29b67a7e9f", {"Hashtag", each Text.Combine(List.Transform(_, Text.From), ","), type text})


There is an example for your reference.
2.JPG1.JPG


Regards,
Lydia

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

Hi Lydia,

 

the code seems to work perfectly fine! Everthything just looks like it should. Thank you so much vor you held! When I apply the query changes though, I get the message as shown below ("the 'ID 2' column does not exist in the rowset"). That is true! But I just pulled the data from a SharePoint-List and did not modify the amount of columns. Also I don't have the description "ID 2" for anything in my code. Can you help me here?

 

Best regards,

Philipp

 

Unbenannt3.PNG

@ottiphi,

In your scenario, delete the extract value step from code in Advanced Editor, then check if you can apply query changes successfully.

If there is no issue, extract values directly from UI following the guide in the screenshots below.
1.JPG

2.JPG

3.JPG

Regards,
Lydia

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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