none
Power query & power pivot - empty table and clearing pivot RRS feed

  • Question

  • Hope you could help. I have the below code in my power query and it is working great with a connected power pivot table. As soon as the Source line is returning "This table is empty" it all goes wrong: 1. Power query return error message that the 2 lines following the source line, are not recognized. 2. The table in the power pivot is showing the last results from the last working query.

    I need the table to be empty if there are no results.

    How do i do that?

    Tnx in advance

    let

    UrlSource = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(UrlSource,{{"Url", type text}}),
    Url = #"Changed Type"{0}[Url],
    
    UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Sagsnummer Type" = Table.TransformColumnTypes(UserInput,{{"Sagsnummer", type text}}),
    Sagsnummer = #"Changed Sagsnummer Type"{0}[Sagsnummer],
    
    Source = OData.Feed(Url & "/FileContacts?$select=CustomLabel_Summary,Name/Name1&$expand=Name&$filter=File/FileNo eq '" & Sagsnummer & "'"),
    #"Expanded Name" = Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Name",{{"CustomLabel_Summary", "Rolle"}, {"Name.Name1", "Kontakt"}})
    

    in

    #"Renamed Columns"
    
    Sunday, February 21, 2016 7:37 PM

Answers

  • You can easily create this table in Power Query, no need to do this in your source.

    If the latest result/table that sits in your Power Pivot Model shall be overwritten by an empty table, you need to create this table. I'm not aware of any alternative to this.

    But it should be quite easy - different to my first suggestion:

    let

    UrlSource = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(UrlSource,{{"Url", type text}}),

    Url = #"Changed Type"{0}[Url],

    UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Sagsnummer Type" = Table.TransformColumnTypes(UserInput,{{"Sagsnummer", type text}}),

    Sagsnummer = #"Changed Sagsnummer Type"{0}[Sagsnummer],

    Source = OData.Feed(Url & "/FileContacts?$select=CustomLabel_Summary,Name/Name1&$expand=Name&$filter=File/FileNo eq '" & Sagsnummer & "'"),

    DummyTable = Table.PromoteHeaders(Table.FromRows({{"Column_1", "Column_2"}})),

    #"Expanded Name" = if Table.RowCount(Source)>0 then Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}) else DummyTable,

    #"Renamed Columns" = Table.RenameColumns(#"Expanded Name",{{"CustomLabel_Summary", "Rolle"}, {"Name.Name1", "Kontakt"}})

    in

    #"Renamed Columns"

    You have to replace the "Column_1", "Column_2"-part by a list of the column names that are returned by the source step. This will create an empty table with exact these column names.

    If there is still an error, try this #"Expanded Name" step instead:

    #"Expanded Name" = try if Table.RowCount(Source)>0 then Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}) else DummyTable otherwise DummyTable,


    Imke Feldmann TheBIccountant.com

    Monday, February 22, 2016 2:29 PM
    Moderator

All replies

  • You should create a HelperTable that has the same columns like your source, but with a dummy row that will return a result once matched with the other 2 queries. As the last step you can delete this row, both events using a conditional statement like:

    if Table.RowCount(Source)<1 then HelperTable else Source


    Imke Feldmann TheBIccountant.com


    Monday, February 22, 2016 6:43 AM
    Moderator
  • You should create a HelperTable that has the same columns like your source, but with a dummy row that will return a result once matched with the other 2 queries. As the last step you can delete this row, both events using a conditional statement like:

    if Table.RowCount(Source)<1 then HelperTable else Source


    Imke Feldmann


    Tnx Imke but that will require me to add tables in my DB.

    Isn't there any way to put the last 2 action lines with if statement?

    Monday, February 22, 2016 1:57 PM
  • You can easily create this table in Power Query, no need to do this in your source.

    If the latest result/table that sits in your Power Pivot Model shall be overwritten by an empty table, you need to create this table. I'm not aware of any alternative to this.

    But it should be quite easy - different to my first suggestion:

    let

    UrlSource = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(UrlSource,{{"Url", type text}}),

    Url = #"Changed Type"{0}[Url],

    UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Sagsnummer Type" = Table.TransformColumnTypes(UserInput,{{"Sagsnummer", type text}}),

    Sagsnummer = #"Changed Sagsnummer Type"{0}[Sagsnummer],

    Source = OData.Feed(Url & "/FileContacts?$select=CustomLabel_Summary,Name/Name1&$expand=Name&$filter=File/FileNo eq '" & Sagsnummer & "'"),

    DummyTable = Table.PromoteHeaders(Table.FromRows({{"Column_1", "Column_2"}})),

    #"Expanded Name" = if Table.RowCount(Source)>0 then Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}) else DummyTable,

    #"Renamed Columns" = Table.RenameColumns(#"Expanded Name",{{"CustomLabel_Summary", "Rolle"}, {"Name.Name1", "Kontakt"}})

    in

    #"Renamed Columns"

    You have to replace the "Column_1", "Column_2"-part by a list of the column names that are returned by the source step. This will create an empty table with exact these column names.

    If there is still an error, try this #"Expanded Name" step instead:

    #"Expanded Name" = try if Table.RowCount(Source)>0 then Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}) else DummyTable otherwise DummyTable,


    Imke Feldmann TheBIccountant.com

    Monday, February 22, 2016 2:29 PM
    Moderator