In Data Explorer, you do not always have to pull stuff in from external sources. You can write text and treat it as a data source or you can just write expressions in the formula bar. In this post we will play with some of the ways you can create data in Data Explorer.

 

#1: Writing simple values down in the formula bar

let’s first create a new blank resource. The formula choice highlighted below allows you to create a new resource with nothing in it. One can then type in the formula bar to define the contents of the resource.

image

Once I select the formula choice we get the blank resource:

image

Note  that the formula bar is blank save for an “=”. For now I will erase the “=” and just type some stuff into the formula bar. Let’s do “Hello World” Montego Style. Erasing the “=” and typing in Hello World we get the following in the preview:

image

What we have is a Resource with a custom task where the value of the custom task is the text we have typed in. Notice that we treat it as text and now you get tools in the ribbon that you can use on the text.

 

#2: Writing simple expressions in the formula bar

let’s change the text to my name and rename the resource (shown in grey on the left) as MyName:

image

if we add another resource we should be able to write an expression to reference the MyName resource. We add a new resource by using the context menu on the little formula icon in the formula bar (left-click on the icon) and choosing New Resource:

image

let’s rename the new resource Hello and then in the formula bar reference the MyName resource by the name of the resource:

image

Notice that there is a subtle difference in our formula this time, we have actually typed in:

= MyName

the “=” indicates we want the expression to be evaluated as opposed to be treated as a literal value. In this case we interpret MyName as a symbol and resolve it to the resource MyName. If we remove the “=” you will just get the literal value MyName:

image

let’s now play around with our ability to write expression here. How about we produce “Hello Tim”. We can use the Text.Combine function to do this… The Text.Combine function can be used to combine a list of values. Here we can combine the literal “Hello” with the value in the MyName resource:

= Text.Combine({"Hello ", MyName})

The result is as follows:

image

For grins we can now build a table and use this to add a calculated column that builds up some text. First lets write down some values to create a table. In this case I have added a new resource and written down some comma separated names and ages:

image

I can then convert this into a table using the Table.FromText function:

image

Selecting “To Table” from the task options yields a builder where I can specify the delimiter and weather the first row should be treated as column headers:

image

Once I have specified Comma as the delimiter and chosen to use the first row as headers I can see the shape of my table (above) and then hit done to accept it. I can now add a calculated column where I can use the Text.Combine to say Hello to each person….

image

Selecting Custom Column allows me to write my desired expression for the column value. I do that here as follows:

image

We can also use the age to customize our greeting. First let’s change the Age to a number. We can use the Change Format task and then use the corresponding builder to change the Age from Text to Number:

image

Now we can create a new custom column that determines the greeting based on whether someone is older or younger than 30. First let’s look at the function:

= each let 
     nickname = if 30 > [#" Age"] then ", laddie" else ", old boy",
     result = "Hello " & [Name] & nickname
in result

we check to see if the age is greater 30 or not and then instead of using the Text.Combine function we use the “&” as the concat operator to concatenate the values that we want. From this we get the following result:

image

 

Hopefully that gave folks a view at the kinds of thing you can do in the formula bar and another little glimpse at where we are going with the product. Notice the updated pixels and styles… this is a build from yesterday’s bits Smile