Show Text when No Data in a Table

Hello Hugh
Thank you very much.
But I am a newbie could. 
Could you please give me a hint how to do that.
Just to know which steps to do previously and where to add your script?
Thank you very much
Juan

Yes sure! Sorry about that.

Here are steps to follow:

1. Go to Power Query Editor (In Power BI, click on Transform Data)

     2. Select your query on in the Query pane on the left.

     3. Click on the Advanced Editor button in the Hom Tab.

     4. Add the Custom line to the M code.

Hugh

Hi Hugh
Thank you but I should add this script on the table that is affected.
This table does not appear on the Query Editor.

As this table is a reference to another one.
How can I add the script.

Could you also give me a hint what the "M code" is?

Regards

Heinrich

Here isthe printscreen from the test.pbix

You see that the table "Table Escalated Filtered" is not shown in the Query Editor.

Hey Heinrich

No, you should preferrably do it at the source which will be the table in your Query Editor. - In my previous message I guide you to get to that.

This is the M code:

The tables in your screenshot are DAX tables - and they are affected by the data that is returned from your Source table.

Here is your table - from the query editor:

I have create an empty table based on your table:

Then I replaced the base64 string that is genreated for that table in the original table:

Then the record returns that the table is empty:

Then if you look at the DAX tables that you created, it will show that the table is empty:

I hope that I am making sense to you.

Hugh

Hi 

Yes, that is correct. Because the "Table Escalated Filtered" table returns data from "Table" you need to make the changes to the source which is "Table", so in the Query Editor you can add that step to the Table query. This will then propegate through to your DAX filtered tables.

Hope it makes sense.

Hugh

Hello Hugh
Thank you for your help
I have done it but the information is not shown.
Could you please share your PBX?
Then I would compare it with mine.
Have a great weekend
Juan

Hey Juan

Yes, sure!

Here is the link to the file: Test.pbix 

Hope you come right with it. Let me know if you don't.

Have a great weekend too!

Hugh

Hello Hugh
Thank you very much.
I have downloaded and tested your Power BI.

But it is empty 

As this visual is from the table

Here the organization

Regards
Juan

Hi Juan

It seems as if I have saved the file with the empty table base64 string. Sorry about that.

I have saved the file with the original base64 string which contains the value, and changes to your DAX table. So feel free to grab the file again.

The main thing to look at is the step in the Applied Steps that checks if the table is empty and adds the row to say the table is empty.

Then in your Table Escalated Filtered Table, you can add 

|| 'Table Escalated'[Tracking-Number] = "Table Is Empty"

to your filter clause:

Table Escalated Filtered = FILTER('Table Escalated','Table Escalated'[Escalated]="false" || 'Table Escalated'[Tracking-Number] = "Table Is Empty")

Then in your visual it will show this:

I hope it is what you are trying to achieve this time. 

Hugh

Hello Hugh

Hope you are having a great time.

Yes this worked. Great thank you.

If I would like to change the source from Excel to SharePoint List would this work

Sourcecode

let
//Base64 string for table with data: "i45WCvE1VNJRckvMKU5Vio0FAA==" 
//Base64 string for table without data: "i44FAA=="
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Tracking-Number" = _t, Escalated = _t]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Tracking-Number", type text}, {"Escalated", type logical}}),
    Custom1 = if Table.IsEmpty(#"Geänderter Typ") then Table.InsertRows(#"Geänderter Typ",0,{ [#"Tracking-Number" = "Table Is Empty",#"Escalated" = "False"]}) else #"Geänderter Typ"
in
    Custom1

to 

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(#"Geänderter Typ") then Table.InsertRows(#"Geänderter Typ",0,{ [#"Tracking-Number" = "Table Is Empty",#"Escalated" = "False"]}) else #"Geänderter Typ"
in
    #"Erweiterte Custom_Summary_as_Text"

Is this ok?

Thank you

Have a great day

Juan

Hello Hugh
I ow you an apology for answering late.
Yes it did work. Fantastic work. Thank you.
Have a great weekend
Juan

Hey Juan

No problem, I am glad to hear that it worked. Have a great weekend too.

Hugh