Combining tables based on #shared output

[ben_langley]

Hi all,

I'm trying to combine tables in a Power BI query based on the table name, only combining them if they are marked with a special character, in this case "*". I'm attempting to do this by getting the list of tables from the "#shared" context, adding a column to identify the source table, then combining them. This works within the Power Query editor, but when applied to Power BI the import fails with an ODBC error as the table objects that were in the #shared record are now null entries.

Combined Tables Query:

let
    Source = #shared,
    #"toTable" = Record.ToTable(Source),
    #"selectRows" = Table.SelectRows(#"toTable", each Text.Contains([Name], "*")),
    #"addColumn" = Table.AddColumn(selectRows, "table", each addTable([Name], [Value])),
    #"combinedTables" = Table.Combine(Table.Column(#"addColumn", "table"))
in
    #"combinedTables"

addTable:

let 
    addTable = (name, value) => Table.AddColumn(value, "table", each stripEnd(name,1))

in
    addTable

 stripEnd

let
    stripEnd = (name, strip) => Text.RemoveRange(name, Text.Length(name)-strip, strip)
in
    stripEnd

Test1*

Name	Value
Spam	Eggs

Test2*

Name	Value
Foo	Bar

Power Query Output:

Name	Value	table
Spam	Eggs	Test1
Foo	Bar	Test2

Power BI Error

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Table..
'.

 Does any one know if this is possible to do within Power BI? Is there something I'm missing? I don't understand why it works successfully within the Power Query editor but then fails on import to Power BI.

Thanks,

Ben

[szabofe]

Hi Everyone, 

I run into this issue also. 
I've modelled 3 scenario. 

1. Static tables within PBI.
2. Tables originated from DataFlow.
3. Tables from Excel.

The same error, except the 1st case.

Here is the link to the sample pbix file: Link

I hope somebody has a resolution.

Regards, 
F.

[vanessafvg]

its actually difficult to know without seeing your data

but from what is saying i would replace your null value with a dummy value, cleary you you have column that has null values pad those with "unknown" or if a number -1 or 99 anything that would not be present in that

data.  you will need to do this before your operations above

[ben_langley]

Hi Vanessa, thanks for replying. The issue also happens with the test example I've shown in the post.