How to split the Trasform Data in correct order ?

[susheeltyagi]

Hi Experts,

I need your help as I am new in this area. I am decoding or importing the data events from Windows App centre to Azure Application insight to Poser BI Dashboard using Export to Power BI (M Query) and in Power BI Dashboard I am using the script generated by App Insigt (Export to Power BI (M Query)) by using Get Data -> Blank Query with Advance Editor.

I am facing the problem while splitting the fields because of Order mismatched . Example ( T, D, Connected, Mode, Build, Firmware ). This is the correct way.

But in some string have order of the Types have been change.

Now how to extract/Parse or split the data in correct order ?

Data Event received while transforme Data :

{"T":"6087D406","D":"090902103","Connected":"True","Mode":"Bluetooth","Build":"549","Firmware":"1E211D06"}    {"T":"6087E8BF","D":"090902103","Connected":"True","Mode":"Bluetooth","Build":"549","Firmware":"1E211D06"}
{"T":"6087F81A","D":"070301682","Connected":"False","Mode":"Bluetooth","Build":"549","Firmware":"1D211D06"}

{"D":"090500925","T":"60871DC0","Connected":"False","Build":"549","Mode":"Bluetooth","Firmware":"1E211D06"}  {"D":"060702821","T":"60882997","Connected":"True","Build":"1","Mode":"Bluetooth","Firmware":"1D211D06"}

Note :- Last two record have ordered distorted.

Expected output should be as below :

Screen Shot

Power BI Dashboard Transform Data Advance Query Editor :

Power BI Script for import data:

/*
The exported Power Query Formula Language (M Language ) can be used with Power Query in Excel
and Power BI Desktop.
For Power BI Desktop follow the instructions below:
1) Download Power BI Desktop from https://powerbi.microsoft.com/desktop/
2) In Power BI Desktop select: 'Get Data' -> 'Blank Query'->'Advanced Query Editor'
3) Paste the M Language script into the Advanced Query Editor and select 'Done'
*/

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/29b8b5f3-9407-4147-8bfe-b07920d85d1c/query",
[Query=[#"query"="customEvents
| where name == 'Devices'
| project custDim = parse_json(tostring(customDimensions))
| project Properties = tostring(custDim.Properties) , tostring(custDim.AppBuild), tostring(custDim.OsVersion), tostring(custDim.Locale), tostring(custDim.OsName), tostring(custDim.SdkName)
, tostring(custDim.SdkVersion), tostring(custDim.OsApiLevel), tostring(custDim.ScreenSize), tostring(custDim.WrapperSdkName), tostring(custDim.WrapperSdkVersion), tostring(custDim.WrapperRuntimeVersion), tostring(custDim.AppNamespace), tostring(custDim.IngressTimestamp)
",#"x-ms-app"="AAPBI",#"timespan"="P1D",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type },
{ "dynamic", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery

Thanks

Susheel

[v-kelly-msft]

Hi @susheeltyagi ，

Check if below thread help:

Pivot Columns Need to be split as separate columns... - Microsoft Power BI Community

Best Regards,
Kelly

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