Forum Discussion
Power query columns containing text data and tables
Good day
I am new to you forum and would greatly appreciate your help. I have a xml. dataset but one of my columns contains tables as well as other text data. If I try to expand the tables, I get an error.
How do I extract the data from the tables while keeping the text data already in the column?
Thank you in advance
Add a new step.
Paste this and alter this to suit your needs.
= Table.TransformColumns(YourPreviousStep, {{"DischargePortATA", each if Value.Is(_, type table) then _ else #table({"DischargePortATA"}, {{_}}) }})
3 Replies
Add a new step.
Paste this and alter this to suit your needs.
= Table.TransformColumns(YourPreviousStep, {{"DischargePortATA", each if Value.Is(_, type table) then _ else #table({"DischargePortATA"}, {{_}}) }})I managed to get this to work, but the results yeild an exponentially large volume of rows, too much to handle, when the results get refreshed back into excel or PowerBi, after I expand the resulting table columns after this step. Could I be missing something?
First, take a look at your xml file, why different data types appear at the same depth of node tree (timestamp and subtree in your example).
Secondly, use Table.ReplaceValue() to substitute Table value with some certain values.
