Show Text when No Data in a Table

[Heinrich]

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 

[Heinrich]

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

[Heinrich]

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

[HughLa]

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]

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

[HughLa]

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

[Heinrich]

Hi Hugh

Thank you I will try it.

Regards

Heinrich

[HughLa]

Let me know if it works! Im curious!

Regards

Hugh

[Heinrich]

Hi Hugh

I tried it but the Table is not showing up, as the table is a reference to another one.

Is it possible to display reference tables on Power Query?

Regards

Heinrich

[HughLa]

Hi Heinrich

Would you be able to share your M code for that query? Just remove any sensitive information.

Hugh

[Heinrich]

Hello Hugh
I made a sample. But can not upload it. Do I have to use DropBox etc.

If you question why I did some extra tables is because of filtering.

Here the explanation. Solved: Re: Filter Data - Microsoft Power BI Community

Thank you

Heinrich

[HughLa]

Hi Heinrich

Yes, can you upload it to DropBox and share the link.

Hugh

[Heinrich]

Hello Hugh

Sorry for the delay.

I uploaded in Gdrive.
Hope you can dowload it.

Have a great time.

Example 

Regards

Juan

[HughLa]

Hi Juan

Cool thanks for the file.

Here is the M code that you can apply to that query:

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvE1VNJRckvMKU5Vio0FAA==", 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

The custom part checks if there is are values in the previous steps, if there isn't it addes the No Data row to the table.

Hope you come right with it.

Hugh

[Heinrich]

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

[HughLa]

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

[Heinrich]

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

[HughLa]

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

[Heinrich]

Here isthe printscreen from the test.pbix

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

[HughLa]

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

[Heinrich]

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