Welcome to 2023, I am looking forward to an amazing year!

I have seen this come up a few times in Twitter where there is an option to put in your own TSQL Query or DAX Query and the input box is very small and often you cannot see the entire query as shown below.

I will show you how to put the entire query in, which will allow you to see the entire query and make it readable.

Here is an example from Ed Ed Hansberry – MVP on Twitter: “#PowerBI hot take incoming…

The example below is a DAX query, but this also applies to SQL Server data sources too.

As you can see above the query scrolls to the side as well as also, I cannot see the entire query.

Below are the steps where I show you how to update this query using the Advanced M query (And don’t worry it is not all that scary)

How to change your query using Power Query Advanced Editor

In the steps below I will show you how to put the entire query in, which will allow you to see the entire query and make it readable.

The first step is to put in your DAX Query as per the screenshot above and click Ok.

I would need to sign in with my Microsoft Account and then connect

In the next window I would then click on Transform Data, so that this would then take me to Power Query

Once in Power Query, for my Query “Query1” I would then click on the Advanced Editor, which would show the advanced editor with the code as shown below.

As you can see above, not only is this hard to read because it is all on one line.

It also has put in all the line feeds #(lf).

The good news is that there is an easier way to view this.

What I do then is to take my existing DAX Query, copy this into Notepad or Notepad ++

I then replace all double quotes with 2 sets of double quotes. The reason for this is because in Power Query the double quotes is a reserved character, so to escape this we need to have two double quotes together.

This is what my initial DAX Query looks like

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('FactInternetSales'[ProductKey], 'FactInternetSales'[OrderQuantity]), "IsGrandTotalRowTotal"
      ),
      "Sales", 'FactInternetSales'[Sales],
      "v_Sales_FormatString", IGNORE('FactInternetSales'[_Sales FormatString])
    ) 

EVALUATE
  __DS0Core

I then do a find and replace for the double quotes as shown below in Notepad ++

I then click on Replace All

Which then changes my query to the following as shown below, where you can see I have got the double quotes repeated twice

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('FactInternetSales'[ProductKey], 'FactInternetSales'[OrderQuantity]), ""IsGrandTotalRowTotal""
      ),
      ""Sales"", 'FactInternetSales'[Sales],
      ""v_Sales_FormatString"", IGNORE('FactInternetSales'[_Sales FormatString])
    )

 

EVALUATE
  __DS0Core

I then go back to my Power Query Advanced Editor.

How where it says [Query=” I then remove all the values until I get to the section at the end which is shown as “, Implementation=”2.0”])

As you can see above, I then have the Query=””

What I then do is to press enter a few times to get the end on another line as shown below.

The final step now is to take my DAX Query from Notepad ++ and insert it in the white space as shown below.

This allows me to see my entire query which is easy to read, and I don’t have to scroll!

As you can see above it says that there are no syntax errors.

I then click Done.

I can then see my query is being successfully loaded.

Summary

In this blog post I have shown you how to put in a query which makes it easier to read when coming back to the query later.

I hope that you found this useful, any questions or comments are most welcome!