Expression.Evaluate + expanding tables showing records correctly in Query Editor but not in dataview

[Niels123]

Hi everyone,

I'm trying to append two identical excel files into one using Expression.Evaluate and expanding the tables afterwards:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Table names", each Expression.Evaluate([Source table], #shared)),
#"Expanded Table names" = Table.ExpandTableColumn(#"Added Custom", "Table names", {"year", "number", "Month", .......}),

The advantage of working this way is you can keep the source of the excel as a column (without having to add extra columns in the source excel files).

I found this solution here:

Solved: Append Table include source name - Microsoft Power BI Community

This gives me the desired result in the Query editor:

But when looking in the data view I only see the names of the two appended excels (so not the expanded/appended records):

Am I doing something wrong?

Thanks for your expertise!

[lbendlin]

Expression.Evaluate is overkill for that.  Create a list of the Excel files you want to append, create a function for the parser, add a custom column calling the parser for each file, then expand that column.

[Niels123]

Thanks for the help! I'll try that out 🙂