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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
nhoff
Advocate I
Advocate I

Expand column where not all records are tables

From an original XML-file I am trying to expand this column: 

 

Udklip1.JPG

When I do this, I get the following result:

 

Udklip2.JPG

 

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:

 

Udklip3.JPG

 

If I now try to expand the column, I get the following error:

Udklip4.JPG

How do I expand the column?

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

View solution in original post

24 REPLIES 24
ImkeF
Super User
Super User

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

Mr_Tank
New Member

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?

Additional Images to be expanded.png

 

Additional Images after being expanded.png

Anonymous
Not applicable

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.

 

Advance editor.png

 

query.png

 

 

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

ImkeF
Super User
Super User

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

PeterT
Frequent Visitor

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

Capture.PNG

 

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

PeterT
Frequent Visitor

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

masplin
Impactful Individual
Impactful Individual

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

masplin
Impactful Individual
Impactful Individual

Hi Imke

 

Seems to make no difference as stil lhave the Error cell on this row of code

 

Capture.PNG

 

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

masplin
Impactful Individual
Impactful Individual

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

 

Capture.PNG

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

masplin
Impactful Individual
Impactful Individual

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.