Expand column where not all records are tables

[nhoff]

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?

[ImkeF]

Hi @Mr_Tank ,
looks like you have a nested table. In that case, just repeat the process.

[Mr_Tank]

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?

 

[Anonymous]

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.

[ImkeF]

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

[ImkeF · Accepted 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"

[PeterT]

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.

[ImkeF]

Hi Pete,

that looks good - & I cannot think of a better way than to merge both columns.

[PeterT]

Thanks Imke, you have really made my day in helping me resolving these issues very fast 

[masplin]

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

[ImkeF]

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 😉

[masplin]

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

[ImkeF]

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({}, {{}}),

[masplin]

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

[ImkeF]

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).
   

[masplin]

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 🙂

[nhoff]

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.

[ImkeF]

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).

[Anonymous]

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

[ImkeF]

Hi Mike,

please have a look if this video helps you:

 

If not, pls come back and I will try differently.

Thx!

[Anonymous]

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