Forum Discussion
Solved
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"
Greg_Deckler
Community Champion
Community ChampionShimonkepha 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 |