Forum Discussion
How to deal with multiple data types in a column: some rows are tables and others text
The ones with tables have values, shown on the preview. I can only drill down or add as new query. How do I get the values from the tables
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"
14 Replies
- Greg_Deckler
Community Champion
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"Thanks so much Greg_Deckler
I used your method with some modifications on the first custom column that finally worked for me. I believe your process works also!
Mod:
if Value.Is([TimephasedData.Value], type text) then [TimephasedData.Value] else 0
- PwerQueryKees
Super User
That'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...
jgeddesAnother method to try using the try otherwise functionality.
Add this step to your query= Table.TransformColumns(previousQueryStep, {{"columnNameWithTables", each try Table.FirstValue(_) otherwise _}})Hi jgeddes
Thanks so much for this but it seems not to work fo me. Here is the link to the file on Google Drive
Hi Greg_Deckler
Thanks so much for this but it seems not to work for me. I wish to attach my PBIX file but I cannot, I have sent you a request to connect on LinkedIn. Here is the link to the file on Google Drive
- dufoq3
Shimonkepha, you havent attached source file. We can't use your pbix file without source MS Project to PBI test Data Date 2025-01-17.xml file...
Here is the source file link https://drive.google.com/file/d/1SGuPwy70JoV_zNbUQb-JaSrqTER8fgNF/view?usp=drive_link
- dufoq3
Hi Shimonkepha, in Assignments table, add this code as a new step:
= Table.FromColumns(List.TransformMany(Table.ToColumns(#"Expanded TimephasedData"), each {Table.Combine(List.Select(_, (x)=> x is table))}, (x,y)=> List.Combine({List.Select(x, (r)=> not (r is table))} & Table.ToColumns(y)) ), {"Value"} )
v-sgandrathiCommunity Support
Hi Shimonkepha ,
Thank you for reaching out Microsoft fabric community forum.
The sample PBIX file provided is inaccessible
Please provide sample data that covers your issue or question completely, in a usable format.Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
How to Get Your Question Answered Quickly - Microsoft Fabric Community
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Regards,
Sahasra.
