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
tloF2D5U9E5
Regular Visitor

Nested tables on only some records

I'm importing list item version history from sharepoint online, so that I can recreate the "Append Changes to Existing Text" functionality of Sharepoint "Multiple lines of text" column type. Unfortunately it's pulling into power Query with multiple data types in one column. If the user enters a return-character then it pulls in as a table, otherwise it immediately pulls in as text. I cannot figure out how to expand the "table" column type and leave the free text as is. Image below. Hopefully it's self explainatory. How can I resolve this?

tloF2D5U9E5_0-1626914009352.png

Thanks!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

See if this helps @tloF2D5U9E5.

NOTE: EDITED - See more streamlined solution below

You need to parse the data out of the Table field, and Text.Combine can do it. Here is the code. Don't worry about all of the stuff at the top. This is just me creating a table that I think mimics your scenario. It is the Query1 line in the steps when you paste it in a blank query (directions on doing this below)

 

let
    Query1 = #table(
            {"ID", "Name", "City"},
                {
                    {123, "Alice", "Wonderand"},
                    {456, "Bob", "Wonderland"},
                    {789, "Jeff", #table(
                        {"Data"},
                        {
                            {"One Line"},
                            {"Two Line"},
                            {"Three Line"}
                        }
                    )
                    }
                }
          ),
    #"Added Custom" = 
        Table.AddColumn(
            Query1, 
            "Custom", 
            each
                if Value.Is([City], type table) 
                then Text.Combine([City][Data], ", ") 
                else [City])
in
    #"Added Custom"

 

Here is the result:

edhans_0-1626917206281.png

The key is this code:

 

if Value.Is([City], type table) 
then Text.Combine([City][Data], ", ") 
else [City])

 

If the value in the City column is a table type, then use Text.Combine([City][Data}, ", ") - what this does is it gets the City table here, and the Data column (yours will be named differently) and returns the values in it as a list. Text.Combine() will take a list and combine them using whatever separator you want.

If the City column is not a table, then it just pulls the text.

STREAMLINED VERSION:
This will use the Replace feature, so you don't generate a new column then have to delete it.

 

let
    Query1 = #table(
            {"ID", "Name", "City"},
                {
                    {123, "Alice", "Wonderand"},
                    {456, "Bob", "Wonderland"},
                    {789, "Jeff", #table(
                        {"Data"},
                        {
                            {"One Line"},
                            {"Two Line"},
                            {"Three Line"}
                        }
                    )
                    }
                }
          ),
    #"Replace Values" =
        Table.ReplaceValue(
            Query1,
            each if Value.Is([City], type table) then [City] else null,
            each Text.Combine([City][Data], ", "),
            Replacer.ReplaceValue,
            {"City"}
        )
in
    #"Replace Values"

 

Before:

edhans_0-1626918038251.png

After:

edhans_1-1626918055820.png
I don't think there is a performance difference, and the code is a bit harder to understand because Table.ReplaceValue has some redundant stuff if working on one column, but it does prevent having to clean up extra columns later. It is the same basic logic though:
Table.ReplaceValue(
     Query1,                                                                           The table you are working on
     each if Value.Is([City], type table) then [City] else null,   if the City column is a table then use that, otherwise null.
     each Text.Combine([City][Data], ", "),                              Combine data from [City] table. if null, no replace done.
     Replacer.ReplaceValue,                                                   Replacement type. You cannot use Replacer.Replacetext.
     {"City"}                                                                            The column you are working on.
)

 


How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

See if this helps @tloF2D5U9E5.

NOTE: EDITED - See more streamlined solution below

You need to parse the data out of the Table field, and Text.Combine can do it. Here is the code. Don't worry about all of the stuff at the top. This is just me creating a table that I think mimics your scenario. It is the Query1 line in the steps when you paste it in a blank query (directions on doing this below)

 

let
    Query1 = #table(
            {"ID", "Name", "City"},
                {
                    {123, "Alice", "Wonderand"},
                    {456, "Bob", "Wonderland"},
                    {789, "Jeff", #table(
                        {"Data"},
                        {
                            {"One Line"},
                            {"Two Line"},
                            {"Three Line"}
                        }
                    )
                    }
                }
          ),
    #"Added Custom" = 
        Table.AddColumn(
            Query1, 
            "Custom", 
            each
                if Value.Is([City], type table) 
                then Text.Combine([City][Data], ", ") 
                else [City])
in
    #"Added Custom"

 

Here is the result:

edhans_0-1626917206281.png

The key is this code:

 

if Value.Is([City], type table) 
then Text.Combine([City][Data], ", ") 
else [City])

 

If the value in the City column is a table type, then use Text.Combine([City][Data}, ", ") - what this does is it gets the City table here, and the Data column (yours will be named differently) and returns the values in it as a list. Text.Combine() will take a list and combine them using whatever separator you want.

If the City column is not a table, then it just pulls the text.

STREAMLINED VERSION:
This will use the Replace feature, so you don't generate a new column then have to delete it.

 

let
    Query1 = #table(
            {"ID", "Name", "City"},
                {
                    {123, "Alice", "Wonderand"},
                    {456, "Bob", "Wonderland"},
                    {789, "Jeff", #table(
                        {"Data"},
                        {
                            {"One Line"},
                            {"Two Line"},
                            {"Three Line"}
                        }
                    )
                    }
                }
          ),
    #"Replace Values" =
        Table.ReplaceValue(
            Query1,
            each if Value.Is([City], type table) then [City] else null,
            each Text.Combine([City][Data], ", "),
            Replacer.ReplaceValue,
            {"City"}
        )
in
    #"Replace Values"

 

Before:

edhans_0-1626918038251.png

After:

edhans_1-1626918055820.png
I don't think there is a performance difference, and the code is a bit harder to understand because Table.ReplaceValue has some redundant stuff if working on one column, but it does prevent having to clean up extra columns later. It is the same basic logic though:
Table.ReplaceValue(
     Query1,                                                                           The table you are working on
     each if Value.Is([City], type table) then [City] else null,   if the City column is a table then use that, otherwise null.
     each Text.Combine([City][Data], ", "),                              Combine data from [City] table. if null, no replace done.
     Replacer.ReplaceValue,                                                   Replacement type. You cannot use Replacer.Replacetext.
     {"City"}                                                                            The column you are working on.
)

 


How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Perfect. Never would have figured that out myself. 

I had to make one modification:
PowerQuery didn't like this. I think it had something to do with the colon.

[properties.Comments]{"Element:Text"}

 

This worked:

Record.Field([properties.Comments]{0},"Element:Text")

I'm still getting an error when trying your fix. It's throwing an error on the first row value of the nested Element:Text column. Any ideas how to fix this?

 

Expression.Error: We cannot convert the value "FirstRowValueofNestedColumn" to type List.
Details:
Value=FirstRowValueofNestedColumn
Type=[Type]

Solved this thanks to @edhans.

Text.Combine([Test.channel.item.labels.label][#"Element:Text"], ", ")

 

Excellent @tloF2D5U9E5 - glad I could help. You might be able to replace the above with {#"Element:Text"} - the # tells PQ to ignore special characters. (not the technical answer, but you can't use "Field/Name" or other special chars without changing to #"Field/Name" sometimes.

 

either way, glad your project is moving forward.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
watkinnc
Super User
Super User

The easiest way I can think of is to duplicate that table, then filter OUT the table(s) in the second table, and filter to JUST the tables in the first table. Then expand the table column in the first table, then append the two tables. 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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.

Top Solution Authors
Top Kudoed Authors