Forum Discussion

Danny26's avatar
Danny26
Frequent Visitor
9 years ago
Solved

How to split Azure resource tags in to separate 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 m...
Need Help
  • Danny26's avatar
    Danny26
    9 years ago

    Hi v-ljerr-msft

     

    Sorry for the multiple posts but I think I have had some success. The code below which includes an IF statement at the start does seem to do the trick and doesn't error out when it can not find the key word. It may not be pretty but it seems to do the job, if you could offer any further refinements that would be great but otherwise I think we might have cracked it. 

     

    Hostname = 
IF(SEARCH( "Hostname" ,'Table1-Full'[Tags],,BLANK() ) ,(
VAR textLength =
    SEARCH (
        ",",
        'Table1-Full'[Tags],
        SEARCH ( "Hostname", 'Table1-Full'[Tags] ) + LEN ( "Hostname" )
            + 3
    )
        - (
            SEARCH ( "Hostname", 'Table1-Full'[Tags] ) + LEN ( "Hostname" )
                + 3
        )
        - 1
RETURN
    MID (
        'Table1-Full'[Tags],
        SEARCH ( "Hostname", 'Table1-Full'[Tags] ) + LEN ( "Hostname" )
            + 3,
        textLength
    )))

     

    Thanks

    Danny

  • Danny26's avatar
    Danny26
    9 years ago

    Hi v-ljerr-msft

     

    Thank you for your help with developing this solution. I have updated the code slightly 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
    )))

     

     

     

Danny26's avatar
Danny26
Frequent Visitor
9 years ago

Hi v-ljerr-msft

 

After a bit more trial and error I have discovered the reason why the original code block doesn't work for me. I have noticed in my data set that I have rows which don't contain the key word hostname and thus the code returns the error 'FIND' could not be found in the given text'. As soon as I have a data set which contains 'Hostname' on every row the code works. I certainly appreciate your help so far so is it possible to wrap up the Find/Search commands with an IFERROR command so it skips over the rows that don't contain the key word. A sample dataset is below, apologies once again for not being to up front with the lay out of the entire data I have only just started out with Power BI. 

 

{"WorkOrder":"6743","Hostname":"Srv04","Project":"1234AB","Location":"Sydney","Workload":"TestApp1"}

{"Hostname":"TestMgmt01","Location":"Sydney","Project":"8015JF","Workload":"TestApp2","Workorder":"7732"}

{"Hostname":"TestNPR01","Location":"Sydney","Project":"1234AA","Workload":"TestApp3","Workorder":"3315"}

{"WorkOrder":"6743","Hostname":"TestSvr02","Project":"1234AB","Location":"Sydney","Workload":"TestApp4"}

{"Hostname":"TestMgmt02","Location":"Sydney","Project":"4455CC","Workload":"TestApp2","Workorder":"7735"}

{"Hostname":"TestMgmt01","Location":"Sydney","Project":"8994TT","Workload":"TestApp2","Workorder":"7732"}

{"WorkOrder":"4467"}

{"Location":"Sydney"}

 

Thanks

Danny

 

Danny26's avatar
Danny26
Frequent Visitor
9 years ago

Hi v-ljerr-msft

 

Sorry for the multiple posts but I think I have had some success. The code below which includes an IF statement at the start does seem to do the trick and doesn't error out when it can not find the key word. It may not be pretty but it seems to do the job, if you could offer any further refinements that would be great but otherwise I think we might have cracked it. 

 

Hostname = 
IF(SEARCH( "Hostname" ,'Table1-Full'[Tags],,BLANK() ) ,(
VAR textLength =
    SEARCH (
        ",",
        'Table1-Full'[Tags],
        SEARCH ( "Hostname", 'Table1-Full'[Tags] ) + LEN ( "Hostname" )
            + 3
    )
        - (
            SEARCH ( "Hostname", 'Table1-Full'[Tags] ) + LEN ( "Hostname" )
                + 3
        )
        - 1
RETURN
    MID (
        'Table1-Full'[Tags],
        SEARCH ( "Hostname", 'Table1-Full'[Tags] ) + LEN ( "Hostname" )
            + 3,
        textLength
    )))

 

Thanks

Danny

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Danny26,

     

    Nice solution!

     

    Could you accept your last reply as solution to help others who may also have the similar issue easily find the answer and close this thread? :smileyhappy:

     

    Regards

    • Danny26's avatar
      Danny26
      Frequent Visitor
      9 years ago

      Hi v-ljerr-msft

       

      Thank you for your help with developing this solution. I have updated the code slightly 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
    )))