Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Danny26
Frequent Visitor

Azure EA tags and how to split them into multiple columns

Hi All,

After much research and trial and error I am trying to figure out a way to parse the Azure resource tags that are exported using the Azure Enterprise connector and use them against other metrics for cost analysis etc. I know you can split the column "Tags" based on a delimiter and assuming the correct tags are applied in the same order for each resource in to separate column's but in our environment the number of tags applied could vary. Below is an example of how the tags are displayed in the exported table.

{"Hostname":"TestSvr01","Location":"Sydney","Project":"12345","Workload":"TestApp1","Workorder":"1234"}

 

What I am trying to achieve is a script or a query which would search for a key word in the Tags column and then place that value into a separate column e.g. Search for the keyword Hostname and place the value next to it in a new column called hostnames. Because a resource/VM could have multiple "Workload" tags I need a method to parse the string instead of using straight delimiters to column process.
Any help with this would be very much appreciated and please ask me to clarify if anything is unclear.
On a side note I am very surprised that MS make reporting on resource tags so difficult.

Thanks
Danny

2 ACCEPTED SOLUTIONS

Hi @Danny26

Please create the following columns.

extractstring = RIGHT(Table3[Tags],LEN(Table3[Tags])+1-SEARCH("HostName",Table3[Tags]))
extractstring1 = IF(
	ISERROR(
		SEARCH("Hostname", Table3[extractstring])
	),
	"",
	RIGHT(Table3[extractstring],LEN(Table3[extractstring])-10)
)
expectedhostname = TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(Table3[extractstring1],""," "),SEARCH(" ",SUBSTITUTE(Table3[extractstring1],","," "))-1),"""",""))


1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-yuezhe-msft

 

I have updated the code to cater for the fact that when a particular Tag I am searching for is at the end of the text string the code would fail because it can't find the last delimitated character ",". So I have changed the delimited character to be ". Now any tag of any length and position within the string can be found and separated out into its own column. e.g. Workload, Location, Hostname. Hopefully this code can be used by others who are trying to report against Azure resource tags. 

 

Hostname = 
IF(SEARCH( "Hostname" ,'All-Azure-Data'[Data.Tags],,BLANK() ) ,(
VAR textLength =
    SEARCH (
        """",
        'All-Azure-Data'[Data.Tags],
        SEARCH ( "Hostname", 'All-Azure-Data'[Data.Tags] ) + LEN ( "Hostname" )
            + 3
    )
        - (
            SEARCH ( "Hostname", 'All-Azure-Data'[Data.Tags] ) + LEN ( "Hostname" )
                + 3
        )
        
RETURN
    MID (
        'All-Azure-Data'[Data.Tags],
        SEARCH ( "Hostname", 'All-Azure-Data'[Data.Tags] ) + LEN ( "Hostname" )
            + 3,
        textLength
    )))

View solution in original post

9 REPLIES 9
Anonymous
Not applicable
NicolaGuarino
New Member

Hi,

I am relatevily new with Power BI.

I have een looking into solutions, at the end I tried myself.


I managed to resolve my issue and I believe this is also solves "partially" the issue described in this post.


I pull the data with Azure Consumption Insight (Beta), and I worked on the Tags column.


I have not split the tags columns in separate columns, but I have been able to extarct the tag that is relevant for me in a new column.

My "Tags" field look like

"{  ""Description"": ""Website"",  ""Department"": ""Communications"",  ""Owner"": ""Jackie Chan"",  ""Environment"": ""Live""}"
"{  ""Department"": ""IT"",  ""Owner"": ""Mickey Mouse"",  ""Environment"": ""PRD"",  ""Description"": ""Inhouse App""}"

 

The "Decsription" is unique in our case (I can't have two values for Description in the tags) and it not always at the same plavce in the Tags column

 

I am interested in the Description value.

 

I have created the following 3 columns

1) # to find wher decsription starts #
StartDescriptionTag = SEARCH("Description",UsageDetails[Tags],1,0)

2)# to find wher decsription ends#

EndDsecriptionTag = IF(SEARCH(",",UsageDetails[Tags],UsageDetails[StartDescriptionTag]+1,0)=0,SEARCH("}",UsageDetails[Tags],UsageDetails[StartDescriptionTag]+1,0),SEARCH(",",UsageDetails[Tags],UsageDetails[StartDescriptionTag]+1,0))
 
And lastly a third column, to extarct the relaant numbers of characters to puluate the final column.
 
MyCorporate_DescriptionTag = IF(UsageDetails[StartDescriptionTag]=0,"",MID(UsageDetails[Tags],UsageDetails[StartDescriptionTag]+15,UsageDetails[EndDsecriptionTag]-UsageDetails[StartDescriptionTag]-16))

The formula can be optimzed and replicated for any other information available in the Tags field.

I needed a quick and dirty solution, I believe I found it and not even that dirty 😉

Ciao

Nicola
 

Hello all, creating columns and writing DAX formulas are great practice but if you want the easy way just transform your tags column  to json, the tags column becomes a record column, then expand the column and select which tags you want as columns, Done!!! make sure you duplicate your tags column before you transform it so you can go back and add tags later.

 

Hope this is useful 

Anonymous
Not applicable

There appears to be a much easier solution using Power BI's JSON parsing function for columns:

 

https://blogs.msdn.microsoft.com/kennethteo/2016/09/15/separating-tags-in-azure-using-free-power-bi-...

v-yuezhe-msft
Employee
Employee

Hi @Danny26,

Do you connect to Azure source in Power BI Desktop? If so,you can firstly split Tags column by left comma in Query Editor as shown in the following screenshot.
1.PNG

Then create a new column using the DAX below.

hostname = IF(
	ISERROR(
		SEARCH("Hostname", Table3[Tags - Copy.1])
	),
	"",
	RIGHT(Table3[Tags - Copy.1],LEN(Table3[Tags - Copy.1])-10)
)

2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

Thank you for the reply I certainly appreciate it. Yes I am connecting to the Azure EA data through the Enterprise connector. 

 

I have tested your solution and it works well using the RIGHT function. What I didn't make clear in my original post and my apologies for that is the tag "Hostname:TestSvr03" could appear anywhere in the string of tags and have a variable length as per below. I don’t think I could do a straight delimitation of the column’s because I cannot guarantee that the Hostname would be placed in the same column each time, because the number of tags applied to resource is also variable.  

 

{"WorkOrder":"6743","Hostname":"TestSvr03","Project":"12345","Location":"Sydney","Workload":"TestApp1"} 

{"WorkOrder":"6743","WorkOrder":"2345","Hostname":"Svr04","Project":"67890","Location":"Sydney","Workload":"TestApplication2"} 

 

I have been putting together a MID command to address this but I am hitting a bit of a hurdle. Here is what I have so far, it searches the row for the Keyword “Hostname” and then extracts 9 characters after it based on the length of “TestSvr01” which is 9 characters long.

 

Hostname = 
IF(FIND("Hostname",'Table1'[Tags],,BLANK()),
MID ( 'Table1'[Tags], FIND("Hostname",Table1'[Tags],,BLANK()) + LEN ( "Hostname" ) + 3, LEN ( "TestSvr01" ) ),"")

 

I would like to add a further bit of logic which caters for a hostname of variable length.  I was going about this by doing another FIND command from the position determined by the first FIND command and locate the end position of the delimited character in this case a comma ",". Once I had that number I was going to subtract the number of characters that make up the word "Hostname" to give me the total number of characters the hostname represents.   So TestSvr01 would return 9 characters and Svr04 would return 5 characters. With the correct number of characters found I was going to pass it to the MID command so it only extracts the right amount of text e.g. only the hostname and no trailing characters.

 

I understand that you cannot pass a variable or an operation in <start_num> section of a FIND command as per FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])  because this command doesn’t work

FIND(",",'Table1'[Tags],FIND("Hostname",'Table1'[Tags],,BLANK()),BLANK())

 

so I am a little bit stumped as to how to calculate the correct number characters in a hostname and pass that to a MID command and extract only the hostname so any further assistance would be greatly appreciated.  Also if you know of any easier way to achieve the same result say with the Power Query command "Text.PositionOf" that would be great as well.

 

Please let me know if any of this is unclear and thanks again for your help.

Hi @Danny26

Please create the following columns.

extractstring = RIGHT(Table3[Tags],LEN(Table3[Tags])+1-SEARCH("HostName",Table3[Tags]))
extractstring1 = IF(
	ISERROR(
		SEARCH("Hostname", Table3[extractstring])
	),
	"",
	RIGHT(Table3[extractstring],LEN(Table3[extractstring])-10)
)
expectedhostname = TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(Table3[extractstring1],""," "),SEARCH(" ",SUBSTITUTE(Table3[extractstring1],","," "))-1),"""",""))


1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

Thank you for the reply I certainly appreciate it, your code did work but I had to make some slight changes to it.  

For some reason my SEARCH/FIND commands don't work unless I include all the arguments and in this case ,,BLANK() on the end as per below. If you know why that is that would be helpful.

 

 

extractstring = RIGHT(Table1[Tags],LEN(Table1[Tags])+1-SEARCH("HostName",Table1[Tags],,BLANK()))
extractstring1 = IF(
	ISERROR(
		SEARCH("Hostname", Table1[extractstring])
	),
	"",
	RIGHT(Table1[extractstring],LEN(Table1[extractstring])-10)
)

For this section I included an "," so it removed the trailing comma.

 

expectedhostname = TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(Table1[extractstring1],","," "),SEARCH(" ",SUBSTITUTE(Table1[extractstring1],","," "),,BLANK())),"""",""))

Thank you again for the code it has been a great help.

 

Regards

Danny

 

Hi @v-yuezhe-msft

 

I have updated the code to cater for the fact that when a particular Tag I am searching for is at the end of the text string the code would fail because it can't find the last delimitated character ",". So I have changed the delimited character to be ". Now any tag of any length and position within the string can be found and separated out into its own column. e.g. Workload, Location, Hostname. Hopefully this code can be used by others who are trying to report against Azure resource tags. 

 

Hostname = 
IF(SEARCH( "Hostname" ,'All-Azure-Data'[Data.Tags],,BLANK() ) ,(
VAR textLength =
    SEARCH (
        """",
        'All-Azure-Data'[Data.Tags],
        SEARCH ( "Hostname", 'All-Azure-Data'[Data.Tags] ) + LEN ( "Hostname" )
            + 3
    )
        - (
            SEARCH ( "Hostname", 'All-Azure-Data'[Data.Tags] ) + LEN ( "Hostname" )
                + 3
        )
        
RETURN
    MID (
        'All-Azure-Data'[Data.Tags],
        SEARCH ( "Hostname", 'All-Azure-Data'[Data.Tags] ) + LEN ( "Hostname" )
            + 3,
        textLength
    )))

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors