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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
valeriminakov
Frequent Visitor

Expand value from table

Hi guys!

Can you help me please?

 

So i've got some XML file then I expanded it.
Here I've got one of columns (year of manufacture) and a lot of values. But I've got some 'table' values and I want to expand it. These tables contain just one value - just year. I've tried to use such fuctions as Text.FromBinary or Table.ExpandTableColumn but I got errors.


How can I expand these some table values?

Thanks.Help pls.png

 

 

 

 

2 ACCEPTED SOLUTIONS

Even better:

 

#"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then Table.FirstValue(_, null) else _}})

 

No need for "try ... otherwise", as a default value can be supplied as second argument that will be returned if the table is empty.

Specializing in Power Query Formula Language (M)

View solution in original post

If all tables are either empty or have 1 column, then you can:

first transform all values to lists and

next use the expand button to expand the column with embedded lists.

 

Generated code:

#"Lists from ndu" = Table.TransformColumns(#"Expanded infoDtp",{{"ndu", each if _ is table then Table.ToList(_) else {_}}}),
#"Expanded ndu" = Table.ExpandListColumn(#"Lists from ndu", "ndu")

 

Specializing in Power Query Formula Language (M)

View solution in original post

10 REPLIES 10
v-sihou-msft
Employee
Employee

@valeriminakov

 

It's quite strange that you keep the table and text into same column since there's no Expand icon on the column header. I can't reproduce this scenario. Can you share your XML file?

 

Regards,

Yep @v-sihou-msft, this file.

List of accident (11-12/2016)

This is open data from russian traffic accident statistics. I guesse it may be a lot of bugs because it takes from differents sources. 

 

To find my example first you need to expand columns: 'tab' then 'infoDtp' then 'ts_info' and finaly it will be column 'g_v' (year of manufactured).

 

Thank You!

After your steps, there are 8 tables in column g_v, of which 7 are empty.

 

You can expand the table with the following code (In which #"Expanded ts_info" is the name of the previous step):

 

    #"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then try Table.FirstValue(_) otherwise null else _}})
Specializing in Power Query Formula Language (M)

Thank you @MarcelBeug so much!

Yep, I've got a lot of empty tables on it, but some have values. When I aggregate more then 100 XML files I realy can lose some important information.
Okay, if I got just one value in such tables I can use Table.FirstValue.
But when I have more then one values in such tables how can i expand all of them?

For example in column -> 'tab' then 'infoDtp' then 'ndu'. In this column I've got already one or more values in one such table. 

 

 

If all tables are either empty or have 1 column, then you can:

first transform all values to lists and

next use the expand button to expand the column with embedded lists.

 

Generated code:

#"Lists from ndu" = Table.TransformColumns(#"Expanded infoDtp",{{"ndu", each if _ is table then Table.ToList(_) else {_}}}),
#"Expanded ndu" = Table.ExpandListColumn(#"Lists from ndu", "ndu")

 

Specializing in Power Query Formula Language (M)

@MarcelBeug thank you for your help! Smiley Happy

Even better:

 

#"Expanded g_v" = Table.TransformColumns(#"Expanded ts_info", {{"g_v", each if _ is table then Table.FirstValue(_, null) else _}})

 

No need for "try ... otherwise", as a default value can be supplied as second argument that will be returned if the table is empty.

Specializing in Power Query Formula Language (M)

@MarcelBeug Hi Marcel,

 

I'm wondering if you can help with this as well, I have a problem where I'm calling data from an API, the API brings back some columns as tables. For agruments sake the column in quesiton is called Field Value I expand the column and I get in some cases text values and in others I get another table. 

 

If I expand the column again the rows with text throw up an error, I've tried to follow what you've provided as the solution but I'm just not sure where to add it (i'm assuming it's in the advanced editor section of power query). What I want to happen is that the table values get expanded and those which are text values stay there and the expanded function on this row is skipped over (to stop the error).

 

I'd be deeply grateful for any help you could give.

 

Regards,

 

Tom

The M code in question is:-

 

#"Expanded get_mail_data.CustomFields.CustomField" = Table.ExpandTableColumn(#"Expanded get_mail_data.CustomFields", "get_mail_data.CustomFields.CustomField", {"Label", "Value"}, {"get_mail_data.CustomFields.CustomField.Label", "get_mail_data.CustomFields.CustomField.Value"}),
ExpandColumn1 = Table.ExpandTableColumn(#"Expanded get_mail_data.CustomFields.CustomField", "get_mail_data.FromUserDetails", {"Name"}, {"get_mail_data.FromUserDetails.Name"}),
ExpandColumn2 = Table.ExpandTableColumn(ExpandColumn1, "get_mail_data.CustomFields.CustomField.Value", {"Element:Text"}, {"get_mail_data.CustomFields.CustomField.Value.Element:Text"}),

When I do ExpandColumn1 I get the mixed results of some Text and some Tables, when I do ExpandColumn2 I can get all the text values that were tables from step ExpandColumn1 but all of the text values through up an error..

You can use Table.FirstValue, like in:

 

let
    Table = #table(type table[Value = any],
        {{"2000"},
         {#table(1,{{"2001"}})},
         {#table(1,{{"2002"}})},
         {"2003"}}),
    ValueFromTable = Table.TransformColumns(Table, {{"Value", each if _ is table then Table.FirstValue(_) else _}})
in
    ValueFromTable
Specializing in Power Query Formula Language (M)

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors