cancel
Showing results for 
Search instead for 
Did you mean: 
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 III
Super User III

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

4 REPLIES 4
edhans
Super User III
Super User III

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

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

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
Solution Sage
Solution Sage

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors