Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thanks!
Solved! Go to Solution.
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:
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:
After:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee 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:
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:
After:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPerfect. 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]
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.