How to deal with multiple data types in a column: some rows are tables and others text

[Shimonkepha]

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

[v-sgandrathi]

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.

[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...

[Shimonkepha]

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"} )

[Shimonkepha]

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

PBI XML File 

[jgeddes]

Another method to try using the try otherwise functionality.
Add this step to your query

= Table.TransformColumns(previousQueryStep, {{"columnNameWithTables", each try Table.FirstValue(_) otherwise _}})

[Shimonkepha]

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

PBI XML File 

[PwerQueryKees]

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

[Shimonkepha]

Hi @PwerQueryKees 

Thanks so much for this but it seems not to work fo me. Here is the link to the file on Google Drive

PBI XML File 

[PwerQueryKees]

You did not include the MS Project export... So I can't see what is happening...

[Shimonkepha]

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

[Greg_Deckler · Accepted solution]

@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"

[Shimonkepha]

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