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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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