Forum Discussion

Heinrich's avatar
Heinrich
Post Partisan
3 years ago
Solved

Show Text when No Data in a Table

Hello

I have a Table-Visual which has "Tracking-Numbers".

Sometimes there are no data then the table is empty.

Is it possible to change the empty table with a table which has a remark "No Data".

Thank you

Heinrich 

Need Help
Show and Tell
Tips and Tricks
  • HughLa's avatar
    HughLa
    3 years ago

    Hi Juan

     

    I am glad that it is finally working!

     

    This code should work for you:

    let
    Quelle = SharePoint.Tables("https://axa365.sharepoint.com/sites/AXA-MicrosoftTeamsService", [Implementation="2.0", ViewMode="All"]),
    #"5aaa8718-9d99-4675-9214-078e6e3131a7" = Quelle{[Id="5aaa8718-9d99-4675-9214-078e6e3131a7"]}[Items],
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"5aaa8718-9d99-4675-9214-078e6e3131a7", "Custom_Summary_as_Text", each Html.Table([Summary],{{"PlainText",":root"}})),
    #"Erweiterte Custom_Summary_as_Text" = Table.ExpandTableColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Custom_Summary_as_Text", {"PlainText"}, {"Custom_Summary_as_Text.PlainText"}),
    Custom1 = if Table.IsEmpty(#"Erweiterte Custom_Summary_as_Text") then Table.InsertRows(#"Erweiterte Custom_Summary_as_Text",0,{ [#"Tracking-Number" = "Table Is Empty",#"Escalated" = "False"]}) else #"Erweiterte Custom_Summary_as_Text"
in
    Custom1

     

    Let me know if it does.

     

    Regards

    Hugh

30 Replies

  • Heinrich's avatar
    Heinrich
    Post Partisan
    3 years ago

    Hi amitchandak

    Thank you but I do not want to remove the table.

    It means that only a row should be show with the remark "No Data"

    Regards

    Heinrich

  • HughLa's avatar
    HughLa
    Resolver IV
    3 years ago

    Hey Heinrich

     

    I have an idea, you can do it in your power query on the data refresh, this will only work with imports. But I am sure you'll be able to achieve this at your sources as well.

     

    I have added this in the Power Query to check if the table is empty then add a row that states the table is empty:

    let
    Source = Excel.Workbook(File.Contents("file.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tracking No", type any}, {"Value", type any}}),
    Custom1 = if Table.IsEmpty(#"Changed Type") then Table.InsertRows(#"Changed Type",0,{ [#"Tracking No" = "Table Is Empty",#"Value" = 0]}) else #"Changed Type"
in
    Custom1

    If there is data the record will not show.

    Hope this will help you!

    Hugh

     

    • Heinrich's avatar
      Heinrich
      Post Partisan
      3 years ago

      Hello Hugh
      This is a cool idea but I pull the data from a sharepoint list. Is it also possible ther?
      Regards
      Heinrich

      • HughLa's avatar
        HughLa
        Resolver IV
        3 years ago

        Yes, it should be possible to do it with a Sharepoint list. 

  • Heinrich's avatar
    Heinrich
    Post Partisan
    3 years ago

    Hello
    Sorry I was on leave.
    I will share a sample today
    Regards
    Heinrich

  • Heinrich's avatar
    Heinrich
    Post Partisan
    3 years ago

    Hi Hugh
    I wanted to do it today but I don't have the rights (company security related).

    Need to do it this evening.
    Take care
    Heinri