cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
elfreako Frequent Visitor
Frequent Visitor

Power BI case insensitive with JSON

I'm connecting to Azure resources via API at resources.azure.com, from there I'm taking the API for Microsoft.Compute and importing all the VM details into Power BI via JSON.

 

The import works fine, however with some situations of the data there is case discrepancy. For example, when working with the tags value, some people have typed the same word but in different case, such as;

 

    "tags": {
      "Project": "DT",
      "SLStandard": "Yes"

compared to;

    "tags": {
      "project": "DT",
      "SlStandard": "Yes"

When expanding the columns out in Power BI it will consider the items listed above as two different value.

 

 

powerbi-case.JPG

 

Ideally I would like to have the JSON imported and the 'case' ignored, or perhaps mark all incoming as either upper or lower case.

 

Here is my Advanced Editor code:

let
    iterations = 10,
    url = 
     "https://management.azure.com/subscriptions/< subscription id >/providers/Microsoft.Compute/virtualMachines?api-version=2017-12-01",

    FnGetOnePage =
     (url) as record =>
      let
       Source = Json.Document(Web.Contents(url)),
       data = try Source[value] otherwise null,
       next = try Source[nextLink] otherwise null,
       res = [Data=data, Next=next]
      in
       res,

    GeneratedList =
     List.Generate(
      ()=>[i=0, res = FnGetOnePage(url)],
      each [i]<iterations and [res][Data]<>null,
      each [i=[i]+1, res = FnGetOnePage([res][Next])],
      each [res][Data]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"tags"}, {"Column1.tags"}),
    #"Expanded Column1.tags" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.tags", {"Project", "project", "SLStandard", "sLStandard", "BIOffline", "bIStandard", "AutomationBI", "biStandard", "BIStandard", "asdf-U001", "TestVM"}, {"Column1.tags.Project.1", "Column1.tags.project", "Column1.tags.SLStandard.1", "Column1.tags.sLStandard", "Column1.tags.BIOffline", "Column1.tags.bIStandard.1", "Column1.tags.AutomationBI", "Column1.tags.biStandard.2", "Column1.tags.BIStandard", "Column1.tags.asdf-U001", "Column1.tags.TestVM"})
in
    #"Expanded Column1.tags"

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Power BI case insensitive with JSON

You can rename your record fields (by applying Text.Proper for example) before expanding (in the last step):

Table.TransformColumns(#"Expanded Column2", {{"tags", each Record.RenameFields(_, List.Zip({Record.FieldNames(_), List.Transform(Record.FieldNames(_), (name)=> Text.Proper(name))}))}}),

 

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




2 REPLIES 2
Super User
Super User

Re: Power BI case insensitive with JSON

You can rename your record fields (by applying Text.Proper for example) before expanding (in the last step):

Table.TransformColumns(#"Expanded Column2", {{"tags", each Record.RenameFields(_, List.Zip({Record.FieldNames(_), List.Transform(Record.FieldNames(_), (name)=> Text.Proper(name))}))}}),

 

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




elfreako Frequent Visitor
Frequent Visitor

Re: Power BI case insensitive with JSON

You legend! Thank you!