Forum Discussion
How to deal with multiple data types in a column: some rows are tables and others text
Shimonkepha I would create 2 new columns that separate out Table values from text values. Then you can work with the Table values without getting errors. Once you have what you need, you can always recombine the 2 columns into a single column once again. PBIX is attached below signature:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJSU1R0lFycQxx1DU0MlaK1YlWCkrNzS/DFHbOz83NLClBSJiYmoElUAwxt7BUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, KEY = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"KEY", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"KEY"}, {{"Table", each _, type table [NAME=nullable text, KEY=nullable text]}}), #"Appended Query" = Table.Combine({#"Grouped Rows", #"Table (2)"}), #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each if Value.Is(Value.FromText([Table]), type text) then [Table] else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Value.Is(Value.FromText([Table]), type text) then null else [Table]), #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"NAME"}, {"NAME"}), #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [Custom] = null then [NAME] else [Custom]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "NAME"}) in #"Removed Columns"
Super UserThat's a challenge...
I would add a step like
Table.TransformColumns("Expanded Time...", {{"Value", each if Value.Is(_, type table) then _ else #table({"TextValue"},{{_}}})
I am not at my laptop, so Ihave not tested this. If you have trouble fixing errors or making it work, feel free to ask for my help.
Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.
Kees Stolker
A big fan of Power Query and Excel
Thanks so much for this but it seems not to work fo me. Here is the link to the file on Google Drive
- PwerQueryKees
You did not include the MS Project export... So I can't see what is happening...
Here is the source file link https://drive.google.com/file/d/1SGuPwy70JoV_zNbUQb-JaSrqTER8fgNF/view?usp=drive_link
- PwerQueryKees
I tried my solution and it works with a small modification:
From
To
With the following code (starting after the 1st screenshot:
... Custom1 = Table.TransformColumns(#"Expanded TimephasedData", {{"Value", each if Value.Is(_, type table) then _ else #table({"Element:Text"},{{_}})}}), #"Expanded Value" = Table.ExpandTableColumn(Custom1, "Value", {"Element:Text"}, {"Element:Text"}) in #"Expanded Value"Basically what Custom1 does is to tranform the values in the column to a table with one row in one column called Element.Text, if the value iis not a table already...
After that it is the standard expand through the UI.
Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.Kees Stolker
A big fan of Power Query and Excel