Forum Discussion

Manar's avatar
Manar
Helper II
8 years ago
Solved

Replace empty table with 0

Hi,

I have a summary table that consist of a union of multiple tables , some of those tables might be empty instead I would like to show 0, but PowerBi doesn't show these table at all (it only showes the one with values in it). is there's a way to replace the empty tables with a zero?

 

 

SummaryTable2 = UNION('AccountingCompany 3','AccountingCurrencyCode 3','ClaimReference 3','ClaimType 3','EntryTime 3','HeaderReference 3','MovementAmountAccounting 3','MovementAmountBase 3','MovementAmountOriginal 3','MovementType 3','OriginalCurrencyCode 3','ReportingPeriod 3','TransactionAmountAccounting 3','TransactionAmountBase 3','TransactionAmountOriginal 3','TransactionDate 3') 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Helly,

     

    What's the data source? Can you add one row of 0s in the data source? Please try the solution below and the demo in the attachment.

    Add one row of 0s in the Query Editor.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    result = Table.InsertRows(Source, 0, {[Name = "0", Value = "0"]}) // Add this line by hand.
in
    result

    Replace_empty_table_with_0

     

    Best Regards,

    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Manar,

     

    If you understand your requirements correctly, you want to show a row of 0s instead of nothing. The workaround is adding them in the source table before union.

     

    Best Regards,

    Dale

    • hellyhan's avatar
      hellyhan
      New Member
      8 years ago

      Thanks for sharing your idea, because I don't have how to replace empty table ? 

       

      Thansk

      Helly

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Helly,

         

        What's the data source? Can you add one row of 0s in the data source? Please try the solution below and the demo in the attachment.

        Add one row of 0s in the Query Editor.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    result = Table.InsertRows(Source, 0, {[Name = "0", Value = "0"]}) // Add this line by hand.
in
    result

        Replace_empty_table_with_0

         

        Best Regards,

        Dale