locked
Please rebuild this data combination - Table.ColumnNames RRS feed

  • Question

  • hi all

    I created a query reading a number of similar from a folder and am using the 'Transform file from...' generated function and sample file to clean up the data once for all files in the folder.

    After i invoke the custom function to read the files, and click the button to expand the content, PQ generates following M code: 

    = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Attainment Reports", Table.ColumnNames(#"Transform File from Attainment Reports"(#"Sample File")))

    This however leads to following error:

    Formula.Firewall: Query 'Attainment Reports' (step 'Expanded Transform File from Attainment Reports') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

    I think the reason it is throwing this error is the fact that the Table.ExpandColumn function is using a reference to the Sample file to get the ColumnNames to be used in the expansion, but I'm not sure what i can do about it to avoid it, as it should really work this way.

    Edit: one small point, not sure if it is related, but the Source step is using a parameter to point to the folder with the source files.

    Appreciate any help or feedback on this.

    Many thanks

    Bart


    • Edited by aikibart Tuesday, January 23, 2018 4:14 PM
    Tuesday, January 23, 2018 10:53 AM

Answers

  • Have you tried splitting this part into another query:

    Query2 = Table.ColumnNames(#"Transform File from Attainment Reports"(#"Sample File"))

    Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Attainment Reports", Query2 )


    Peter Q. http://blogs.msdn.com/peter_qian

    Wednesday, January 31, 2018 1:32 AM

All replies

  • Would anyone have any idea where to look? Could it be a bug and should i consider not to use the automatically created function that reads the excel file?

    thanks

    Bart

    Monday, January 29, 2018 9:41 AM
  • Have you tried splitting this part into another query:

    Query2 = Table.ColumnNames(#"Transform File from Attainment Reports"(#"Sample File"))

    Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Attainment Reports", Query2 )


    Peter Q. http://blogs.msdn.com/peter_qian

    Wednesday, January 31, 2018 1:32 AM
  • hi Peter

    First off, my apologies for the ridiculously late reply.

    I tried your proposal and it worked. However, after doing a change to the "Transform Sample File from Attainment Reports" query, it stopped working again. After then closing the file without saving, and re-opening, and retrying the whole thing, it no longer works.

    The weird thing is that when i load the data to a table, it does seem to work, but it keeps throwing the error.

    Could this be a bug?

    For now, I'm not going to use the Table.ColumnNames function to get the names dynamically from the sample file, but will just hardcode the column names.

    Thanks

    Bart




    • Edited by aikibart Tuesday, March 13, 2018 4:03 PM
    Tuesday, March 13, 2018 3:04 PM
  • Hi Bart,

    Is there any chance that you can provide the actual M for the query that you get this error on and the queries that it depends on? Feel free to replace any confidential aspects with pseudonyms.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, March 28, 2018 1:49 PM