Power BI case insensitive with JSON

[elfreako]

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.

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.

[ImkeF · Accepted solution]

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

[elfreako]

You legend! Thank you!