none
Query on view returning null values in Power Query where there are numbers RRS feed

  • Question

  • I have the following query that gets the database sizes. 

    with fs
    as
    (
        select database_id, type, size * 8.0 / 1024 size
        from sys.master_files
    )
    select 
        name,
        (select sum(size) from fs where type = 0 and fs.database_id = db.database_id)  DataFileSizeMB,
        (select sum(size) from fs where type = 1 and fs.database_id = db.database_id)  LogFileSizeMB
    from sys.databases db

    When I run this in SQL Server I get results for my various databases. When I run it in Power Query I get the database names but the size columns are filled with null's. I put this query in a view and had Power Query use the view and the same thing happens. Why is this happening?

    Monday, July 7, 2014 1:58 PM

Answers

  • I tried to execute the same query in my local SQL server. I am getting the same results in both SQL and PowerQuery. Please check whether the account you are using in PowerQuery has proper permissions to access system tables.
    • Marked as answer by ClintDavis Tuesday, July 8, 2014 5:54 PM
    Tuesday, July 8, 2014 11:03 AM