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
From an original XML-file I am trying to expand this column:
When I do this, I get the following result:
Some records are not at the 'text'-level, some are still tables. If I mark one of the records with table value, I can see the following content:
If I now try to expand the column, I get the following error:
How do I expand the column?
Solved! Go to Solution.
This seems to be a bit tricky. You add a column that checks if the content of the current row of the column to expand is of type table. In that case you take the value as it is and if not, you transform the text-value to table. That way all fields will be in table format and can be expanded:
let Source = {1, #table({"a", "A"}, {{"b", "B"}})}, #"In Tabelle konvertiert" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})), #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom]))) in #"Erweiterte Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Mr_Tank ,
looks like you have a nested table. In that case, just repeat the process.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF, I see you're very helpful with this issue. My issue is that none of the codes above resolved my issue. I have "null" and "Table" in my column "Additional Images" before and after expansion. See screenshots below. The tables ( with the "Element:Text" records inside) must be expanded to new columns. Can you please help?
Hi,
I've read all the posts but as I don't M language.... I can't think of how to integrate the given solutions. Also, in my case is the other way around. See the picture below. My error is at row 361. It cannot convert a the value in column "element.text" to table, so I can't load the query. However, as you can see I have serveral columns with "table" or "text". In my case, I want the text within each table (see picture), instead of converting the text to "Tables.". I have also attached the editor.
Thanks.
Hi @Anonymous ,
please check if the instructions from this video help integrating the M-code into your solution. https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This seems to be a bit tricky. You add a column that checks if the content of the current row of the column to expand is of type table. In that case you take the value as it is and if not, you transform the text-value to table. That way all fields will be in table format and can be expanded:
let Source = {1, #table({"a", "A"}, {{"b", "B"}})}, #"In Tabelle konvertiert" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})), #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom]))) in #"Erweiterte Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke,
I have followed your advice and as a result I managed to transform data from one column that had both table and integer types in the original data set into two new columns: Classification.I... > Custom.ConvertedText & Custom.Element:Text
The original data and resulting columns looks like this
As an end result, I would like to have just one column. Just wanted to know if merging these two columns (Custom.ConvertedText & Custom.Element:Text) would be my best option or is there another (better) way to achieve the same?
FYI: this is continuation to this thread.
Hi Pete,
that looks good - & I cannot think of a better way than to merge both columns.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks Imke, you have really made my day in helping me resolving these issues very fast
Hi Imke
Tryin to folow your code, but not working for me. In the AddedCustom1 row of my code i'm calling a function that seaches for data withiin a web page, but not every row has a result so some show an error instead of Table.
I'm not at all sure how to integrate your suggested code to check if the result of added Custom1 is a table or not and then to expand out the table in expanded Custom1?
I just get expression error we cannot convert a vlaue of type table to type list for the In Tabelle konvertiert line. So presumably I dont have a list in the step above to use Table.FromList.
let Source = Excel.Workbook(File.Contents("U:\Dropbox\Limerston\Crawford\CRO Companies.xlsx"), null, true), CRO_Company_Table = Source{[Item="CRO_Company",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(CRO_Company_Table,{{"Company", type text}, {"Address", type text}, {"Search", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web_Main([Search])), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Company", Order.Ascending}}), #"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows1", "Custom", {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}, {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Web_Description([Search])), #"In Tabelle konvertiert" = Table.FromList( #"Added Custom1", Splitter.SplitByNothing(), null, null, ExtraValues.Error), Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})), #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom]))), #"Expanded Custom1" = Table.ExpandTableColumn( #"Erweiterte Custom", "Custom", {"Description"}, {"Description"}),
The error I'm trying to handle looks like this in the preceeding step (cant insert an image for some reason)
Table
Error
Table
Table
Error
Hi @masplin,
you can use the error-handler "try .... otherwise" like so:
#"In Tabelle konvertiert" = try Table.FromList( #"Added Custom1", Splitter.SplitByNothing(), null, null, ExtraValues.Error) otherwise #"Added Custom1",
It tries to convert the list into a table and if the list isn't a list (but a table instead), it uses just this table 😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke
Seems to make no difference as stil lhave the Error cell on this row of code
The error says this
An error occurred in the ‘’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
Table
Thanks
Mike
Yes, because my understanding of your situation wasn't correct: On the picture I can see that the error is caused by your function already and not by the step you've mentioned.
So you could put into the "otherwise" route an empty table instead:
#"In Tabelle konvertiert" = try Table.FromList( #"Added Custom1", Splitter.SplitByNothing(), null, null, ExtraValues.Error) otherwise #table({}, {{}}),
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Sorry Imke I'm being quite stupid. I added that line and now get just a single cell and have lost the 200 rows of my table. As you say the error is actually caused in the function I'm calling right back in the navigation stage
No problem, you're welcome.
Yes, I didn't pay enough attention to it. This step looks useless, you should delete it.
Then adjust the next step like so:
Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each try Table.AddColumn([Column1], "Dummy", each 1) otherwise #table({"ConvertedText"}, {{[Column1]}})),
So instead of using if then else (which won't let you get rid of the error) , you use the error-handler an try to do a command on a table (here I added a dummy-column that you can later delete, but you can easily replace it by a format conversion on an existing column, then you don't have to reverse this operation).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I'm not sure what a format conversion is? I'm also not sure i'm putting your steps in the correct order as all getting a bit fragmented. I've got this code whch now gives me a "Custom10" column where the "Error" are all now "Table". I'm not sure what "Column1" is?
let Source = Excel.Workbook(File.Contents("U:\Dropbox\Limerston\Crawford\CRO Companies.xlsx"), null, true), CRO_Company_Table = Source{[Item="CRO_Company",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(CRO_Company_Table,{{"Company", type text}, {"Address", type text}, {"Search", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web_Main([Search])), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Company", Order.Ascending}}), #"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows1", "Custom", {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}, {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Web_Description([Search])), Step2 = Table.AddColumn( #"Added Custom1", "Custom10", each try Table.AddColumn([Column1], "Dummy", each 1) otherwise #table({"ConvertedText"}, {{[Column1]}})) in Step2
However if I try to expand this column I get
Expression.Error: The field 'Column1' of the record wasn't found.
Details:
Company=3Brain
Address=3Brain GmbH Schulstrasse 76 7302 Landquart Switzerland
Search=3brain
Website
=3brain.com
Headquarters
sorry for being dim, but seems you are close to the solution just my "M" is too poor to understand it 🙂
Thanks a lot, this approach worked.
I did not completely follow the code of the addcolumn step, where there is a reference to #table({"ConvertedText"} - where did that come from?
Nevertheless I managed to copy and replace with my own values and it worked.
Great that you got it working.
That's the part where the text gets converted to a table. "ConvertedText" is jut the column name I choose (as it mustn't be a name that already exists in the other columns of the table - otherwise the expand-step with dynamic column names wouldn't work).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
I'm having this same problem. At what point in the advnaced editor do I need to insert your code?
Sorry for the dumb question but I am new to Power BI.
Thanks
Mike
Hi Mike,
please have a look if this video helps you:
If not, pls come back and I will try differently.
Thx!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi ImkeF.
I have inserted your code into a new query. Do I just need to change the source to my source?
I'm unsure as to amend your code so that it works on my source data.
Sorry for the dumb questions, I am new to Power BI!
Regards
Mike
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
161 | |
129 | |
129 | |
92 | |
91 |