Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You legend! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |