Forum Discussion
Combining tables based on #shared output
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
3 Replies
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
Hi Vanessa, thanks for replying. The issue also happens with the test example I've shown in the post.
