[CoE Starter Kit - BUG] DF CoE BYODL Makers - We found extra characters at the end of the JSON input

[miroslav-harlas]

Does this bug already exist in our backlog?

• [X] I have checked and confirm this is a new bug.

Describe the issue

Hello, I can see that there were several similar issues submitted in the past in 2023, but I face it again with upgrade to version March 2024 (4.24). I'm not sure what to do now. Issue occurs in Dataflow "CoE BYOD Makers". I opened Dataflow and I can see in PowerQry builder that issue is in the generated table "Makers" on the second step "Merged queries".

I'm attaching screenshots and copy of the error message.

Please, advice what we can do to get this fixed.

`` ---------- Message ---------- DataFormat.Error: We found extra characters at the end of the JSON input.

---------- Session ID ---------- 8d5ace9c-6a33-4d67-95f9-6ea8954b6f4e

---------- Request ID ---------- f930b18c-ec55-48fa-a0cd-d144dbca67b7

---------- Mashup script ---------- section Section1; shared #"Transform Sample file (4)" = let Source = Table.FromColumns({Lines.FromBinary(#"Parameter (4)")}),

"Transformed Column" = Table.TransformColumns(Source, {"Column1", Json.Document}),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Transformed Column", "Column1", {"environmentId", "environmentName", "isDefault", "environmentState", "environmentUrl", "environmentType", "securityGroup", "purpose", "cdsInstanceId", "cdsInstanceUrl", "createdTime", "createdPrincipalId", "lastModifiedTime", "lastModifiedPrincipalId", "deletedTime", "environmentRegion", "tenantGuid"}, {"environmentId", "environmentName", "isDefault", "environmentState", "environmentUrl", "environmentType", "securityGroup", "purpose", "cdsInstanceId", "cdsInstanceUrl", "createdTime", "createdPrincipalId", "lastModifiedTime", "lastModifiedPrincipalId", "deletedTime", "environmentRegion", "tenantGuid"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {{"environmentId", type text}, {"environmentName", type text}, {"isDefault", type logical}, {"environmentState", type text}, {"environmentUrl", type text}, {"environmentType", type text}, {"securityGroup", type text}, {"purpose", type text}, {"cdsInstanceId", type text}, {"cdsInstanceUrl", type text}, {"createdTime", type datetime}, {"createdPrincipalId", type text}, {"lastModifiedTime", type text}, {"lastModifiedPrincipalId", type text}, {"deletedTime", type text}, {"environmentRegion", type text}, {"tenantGuid", type text}})

in

"Changed Type";

shared #"Sample file" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerapps/Applications"),

"Filtered hidden files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

Navigation = #"Filtered hidden files"{0}[Content]

in Navigation; shared #"Transform file" = let Source = (Parameter as binary) => let Source = Text.FromBinary(Parameter),

"Imported JSON" = Replacer.ReplaceText(Source, "}", "},"),

#"Trim last character" = Text.Trim(#"Imported JSON", ","),
#"Insert bracket" = Text.Insert(#"Trim last character", 0, "["),
#"Insert second bracket" = Text.Insert(#"Insert bracket", Text.Length(#"Insert bracket"), "]"),
#"Parsed JSON" = Json.Document(#"Insert second bracket")

/* #"Navigation 1" = #"Parsed JSON"{0}

"Converted to table" = Record.ToTable(#"Navigation 1"),

#"Transposed table" = Table.Transpose(#"Converted to table"),
#"Promoted headers" = Table.PromoteHeaders(#"Transposed table", [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"resourceId", type text}, {"environmentId", type text}, {"type", type text}, {"subType", type text}, {"DocumentVersion", type text}, {"name", type text}, {"description", type text}, {"uri", type text}, {"tenantId", type text}, {"lifecycleState", type text}, {"owner", type text}, {"createdTime", type datetime}, {"createdPrincipalId", type text}, {"lastModifiedTime", type datetime}, {"lastModifiedPrincipalId", type text}, {"lastEnabledTime", type datetime}, {"lastEnabledPrincipalId", type text}, {"sharedUsers", Int64.Type}, {"sharedGroups", Int64.Type}, {"solution", type any}}) */

in

"Parsed JSON"

in Source; shared #"Sample file (9)" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerautomate/Flows"),

"Filtered hidden files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

Navigation = #"Filtered hidden files"{0}[Content]

in Navigation; shared #"Transform file (9)" = let Source = (Parameter as binary) => let Source = Text.FromBinary(Parameter),

"Imported JSON" = Replacer.ReplaceText(Source, "}", "},"),

#"Trim last character" = Text.Trim(#"Imported JSON", ","),
#"Insert bracket" = Text.Insert(#"Trim last character", 0, "["),
#"Insert second bracket" = Text.Insert(#"Insert bracket", Text.Length(#"Insert bracket"), "]"),
#"Parsed JSON" = Json.Document(#"Insert second bracket")

in

"Parsed JSON"

in Source; shared #"Sample file (5)" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerapps/environments"),

"Filtered hidden files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

Navigation = #"Filtered hidden files"{0}[Content]

in Navigation; shared #"Transform file (5)" = let Source = (Parameter as binary) => let Source = Table.FromColumns({Lines.FromBinary(Parameter)}),

"Transformed Column" = Table.TransformColumns(Source, {"Column1", Json.Document}),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Transformed Column", "Column1", {"environmentId", "environmentName", "isDefault", "environmentState", "environmentUrl", "environmentType", "securityGroup", "purpose", "cdsInstanceId", "cdsInstanceUrl", "createdTime", "createdPrincipalId", "lastModifiedTime", "lastModifiedPrincipalId", "deletedTime", "environmentRegion", "tenantGuid"}, {"environmentId", "environmentName", "isDefault", "environmentState", "environmentUrl", "environmentType", "securityGroup", "purpose", "cdsInstanceId", "cdsInstanceUrl", "createdTime", "createdPrincipalId", "lastModifiedTime", "lastModifiedPrincipalId", "deletedTime", "environmentRegion", "tenantGuid"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {{"environmentId", type text}, {"environmentName", type text}, {"isDefault", type logical}, {"environmentState", type text}, {"environmentUrl", type text}, {"environmentType", type text}, {"securityGroup", type text}, {"purpose", type text}, {"cdsInstanceId", type text}, {"cdsInstanceUrl", type text}, {"createdTime", type datetime}, {"createdPrincipalId", type text}, {"lastModifiedTime", type text}, {"lastModifiedPrincipalId", type text}, {"deletedTime", type text}, {"environmentRegion", type text}, {"tenantGuid", type text}})

in

"Changed Type"

in Source; shared #"Sample file (4)" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerapps/environments"),

"Filtered hidden files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

Navigation = #"Filtered hidden files"{0}[Content]

in Navigation; shared #"Parameter (4)" = let Parameter = #"Sample file (4)" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary] in Parameter; shared #"Transform file (4)" = let Source = (Parameter as binary) => let Source = Table.FromColumns({Lines.FromBinary(Parameter)}),

"Transformed Column" = Table.TransformColumns(Source, {"Column1", Json.Document}),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Transformed Column", "Column1", {"environmentId", "environmentName", "isDefault", "environmentState", "environmentUrl", "environmentType", "securityGroup", "purpose", "cdsInstanceId", "cdsInstanceUrl", "createdTime", "createdPrincipalId", "lastModifiedTime", "lastModifiedPrincipalId", "deletedTime", "environmentRegion", "tenantGuid"}, {"environmentId", "environmentName", "isDefault", "environmentState", "environmentUrl", "environmentType", "securityGroup", "purpose", "cdsInstanceId", "cdsInstanceUrl", "createdTime", "createdPrincipalId", "lastModifiedTime", "lastModifiedPrincipalId", "deletedTime", "environmentRegion", "tenantGuid"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {{"environmentId", type text}, {"environmentName", type text}, {"isDefault", type logical}, {"environmentState", type text}, {"environmentUrl", type text}, {"environmentType", type text}, {"securityGroup", type text}, {"purpose", type text}, {"cdsInstanceId", type text}, {"cdsInstanceUrl", type text}, {"createdTime", type datetime}, {"createdPrincipalId", type text}, {"lastModifiedTime", type text}, {"lastModifiedPrincipalId", type text}, {"deletedTime", type text}, {"environmentRegion", type text}, {"tenantGuid", type text}})

in

"Changed Type"

in Source; shared Makers = let Source = OData.Feed(EnvironmentAPI & "/aadusers", null, [Implementation = "2.0"]),

"Merged queries" = Table.NestedJoin(Source, {"aaduserid"}, #"Combined Makers", {"createdBy"}, "Combined Makers", JoinKind.RightOuter),

#"Expanded Combined App and Flow Makers" = Table.ExpandTableColumn(#"Merged queries", "Combined Makers", {"createdBy"}, {"createdBy"}),

"Inserted IsOrphan" = Table.AddColumn(#"Expanded Combined App and Flow Makers", "Is Orphan", each if [aaduserid] = null then true else if [aaduserid] <> null then false else ""),

"Removed unneeded columns" = Table.RemoveColumns(#"Inserted IsOrphan", {"postalcode", "businessphones", "streetaddress", "mobilephone", "surname", "givenname", "createddatetime", "id", "imaddresses"}),

"Get distinct createdBy" = Table.Distinct(#"Removed unneeded columns", {"createdBy"}),

"Replaced mail value" = Table.ReplaceValue(#"Get distinct createdBy", null, "", Replacer.ReplaceValue, {"mail"}),

// SYSTEM maker will be added with it's own unique GUID in the setup wizard

"Filtered out SYSTEM maker" = Table.SelectRows(#"Replaced mail value", each ([createdBy] <> "SYSTEM")),

"Replaced UPN" = Table.ReplaceValue(#"Filtered out SYSTEM maker", null, "", Replacer.ReplaceValue, {"userprincipalname"}),

"Replaced preferred language" = Table.ReplaceValue(#"Replaced UPN", null, "", Replacer.ReplaceValue, {"preferredlanguage"}),

"Filtered blank makers" = Table.SelectRows(#"Replaced preferred language", each ([createdBy] <> null)),

"Replaced null display name with Unknown" = Table.ReplaceValue(#"Filtered blank makers", null, "Unknown", Replacer.ReplaceValue, {"displayname"}),

"Changed column type" = Table.TransformColumnTypes(#"Replaced null display name with Unknown", {{"createdBy", type text}})

in

"Changed column type";

shared #"App Makers" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerapps/Applications"), excludehiddenfiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), excludezerolengthfiles = Table.SelectRows(excludehiddenfiles, each [Attributes]?[Size]? > 0),

"Replaced canvas.json" = Table.ReplaceValue(excludezerolengthfiles, "canvas.json", "", Replacer.ReplaceText, {"Name"}),

"Replaced model.json" = Table.ReplaceValue(#"Replaced canvas.json", "model.json", "", Replacer.ReplaceText, {"Name"}),

"Lowercased environment name" = Table.TransformColumns(#"Replaced model.json", {{"Name", each Text.Lower(_), type nullable text}}),

"Merged queries" = Table.NestedJoin(#"Lowercased environment name", {"Name"}, Environments, {"environmentId"}, "Environments", JoinKind.Inner),

  Getcontent = Table.AddColumn(#"Merged queries", "Transform file", each #"Transform file"([Content])),

"Expanded file" = Table.ExpandListColumn(Getcontent, "Transform file"),

"Expanded createdBy" = Table.ExpandRecordColumn(#"Expanded file", "Transform file", {"createdPrincipalId"}, {"createdBy"}),

#"Removed columns" = Table.RemoveColumns(#"Expanded createdBy", {"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Name", "Content", "Environments"}),

"Removed duplicates" = Table.Distinct(#"Removed columns", {"createdBy"}),

"Filtered rows" = Table.SelectRows(#"Removed duplicates", each [createdBy] <> null)

in

"Filtered rows";

shared #"Environment Makers" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerapps/environments"), excludehiddenfiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), excludezerolengthfiles = Table.SelectRows(excludehiddenfiles, each [Attributes]?[Size]? > 0),

"Get file content" = Table.AddColumn(excludezerolengthfiles, "Transform file", each #"Transform file (5)"([Content])),

"Expanded Transform file" = Table.ExpandTableColumn(#"Get file content", "Transform file", {"createdPrincipalId"}, {"createdBy"}),

"Removed columns" = Table.RemoveColumns(#"Expanded Transform file", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),

"Removed duplicates" = Table.Distinct(#"Removed columns", {"createdBy"}),

"Filter out duplicates" = Table.SelectRows(#"Removed duplicates", each [createdBy] <> null),

"Filtered out Microsoft Dynamics Deployment Service maker" = Table.SelectRows(#"Filter out duplicates", each [createdBy] <> "Microsoft Dynamics Deployment Service")

in

"Filtered out Microsoft Dynamics Deployment Service maker";

shared #"Flow Makers" = let Source = AzureStorage.DataLake(DatalakeURL & "/powerautomate/Flows"), excludehiddenfiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), excludezerolengthfiles = Table.SelectRows(excludehiddenfiles, each [Attributes]?[Size]? > 0),

"Replaced .json" = Table.ReplaceValue(excludezerolengthfiles, ".json", "", Replacer.ReplaceText, {"Name"}),

"Lowercased environment name" = Table.TransformColumns(#"Replaced .json", {{"Name", each Text.Lower(_), type nullable text}}),

"Merged with environments" = Table.NestedJoin(#"Lowercased environment name", {"Name"}, Environments, {"environmentId"}, "Environments", JoinKind.Inner),

Getcontent = Table.AddColumn(#"Merged with environments", "Transform file", each #"Transform file (9)"([Content])),

"Removed errors" = Table.RemoveRowsWithErrors(Getcontent, {"Transform file"}),

"Removed columns" = Table.RemoveColumns(#"Removed errors", {"Folder Path", "Attributes", "Date created", "Date modified", "Date accessed", "Extension", "Environments"}),

"Expanded Transform file" = Table.ExpandTableColumn(#"Removed columns", "Transform file", {"events_created_principalId"}, {"createdBy"}),

"Removed duplicates" = Table.Distinct(#"Expanded Transform file", {"createdBy"}),

"Removed columns 1" = Table.RemoveColumns(#"Removed duplicates", {"Content", "Name"}),

"Filtered rows" = Table.SelectRows(#"Removed columns 1", each [createdBy] <> null)

in

"Filtered rows";

[Description = "Environment Web API endpoint. Get via make.powerapps.com > Settings Cog > Developer resources > Web API Endpoint value (example https://mycoe.api.crm.dynamics.com/api/data/v9.2)"] shared EnvironmentAPI = let EnvironmentAPI = https://intentionally-replaced-due-to-sharing-on-github.crm.dynamics.com/api/data/v9.2 meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type any] in EnvironmentAPI; shared DatalakeURL = let DatalakeURL_Apps = https://intentionally-replaced-due-to-sharing-on-github.dfs.core.windows.net/powerplatform meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type any] in DatalakeURL_Apps; shared #"Combined Makers" = let Source = Table.Combine({#"Environment Makers", #"App Makers", #"Flow Makers"}),

"Removed duplicates" = Table.Distinct(Source, {"createdBy"})

in

"Removed duplicates";

shared Environments = let Source = AzureStorage.DataLake(DatalakeURL & "/powerapps/environments"), excludehiddenfiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), excludezerolengthfiles = Table.SelectRows(excludehiddenfiles, each [Attributes]?[Size]? > 0),

"Get file content" = Table.AddColumn(excludezerolengthfiles, "Transform file", each #"Transform file (5)"([Content])),

"Expanded Transform file" = Table.ExpandTableColumn(#"Get file content", "Transform file", {"environmentId"}, {"environmentId"}),

"Choose columns" = Table.SelectColumns(#"Expanded Transform file", {"environmentId"})

in

"Choose columns";

``

Expected Behavior

No response

What solution are you experiencing the issue with?

Core

What solution version are you using?

4.24

What app or flow are you having the issue with?

DF - CoE BYODL Makers

What method are you using to get inventory and telemetry?

Data Export

Steps To Reproduce

No response

Anything else?

No response

AB#2778

[Jenefer-Monroe]

Unfortunately this means that either your Data Export files are corrupt or there has been a schema change we are unaware of. As I dont repro I presume your Data Export files are corrupt.

Please go to the Datalake and open files from the different folders (ex here from the flows container)

Is the file formatted like image 1 with a single line per flow or like image 2 with a jumbled look? Image 1 : Well formatted Image 2 : Not well formatted

[miroslav-harlas]

Hello @Jenefer-Monroe , thank you very much for a prompt response. We have hundreds of files located in this folder. Do you mean that we should check each and every file in this folder?

I have went trough few files randomly and I found files with these two different types of JSON formatting, when opened in VSCode:

[Jenefer-Monroe]

OK yes it looks like you have an unexpected node in there. Can you please do the following

1. Note that "last modified" date for the file with the "customExtensions" node
2. Go the Command Center and delete back to that date minus one
3. Open the CoE BYODL Flows dataflow and go to the point shown. You should see only these two columns. Is that what you see?

[miroslav-harlas]

Hello, thank you for hints.

Ad 2. I would like to clarify that we haven't successfully integrated Starter Kit with Data Lake yet. We tried that with September 2023 version and failed, and then continued with cloud-flow inventory. As far as there were several bugs with identical description of the issue, we also skipped couple of versions. We have updated to March 2024 recently and trying Data Lake integration again. We have nothing else in the history than that one failed run of the Maker Dataflow. I apologize if my previous description of the scenario was misleading.

Ad 1. Last Modified Date of the file with different structure is: 11/21/2022

Ad 3. Yes we can see only two columns

4. We have been following process described here: https://learn.microsoft.com/en-us/power-platform/guidance/coe/setup-core-components#configure-connections-to-data-sources We started with just Maker dataflow and having the error there. We haven't tried to configure other dataflows yet, as the guide is describing the order in which dataflows should be configured. When having Flow dataflow open in Edit mode and updating parameters and connections, there is no error displayed. However I haven't tried to publish and execute it as far as Maker dataflow hasn't been successfully completed yet.

[Jenefer-Monroe]

Gotcha. OK I would advice continuing with the Sync Flow architecture for now then. There is a big schema change coming in the Data Export world and so we will be doing a FC to respond to that,

[miroslav-harlas]

Oh, OK. Thank you very much for your piece of advice. We would adapt our internal tasks and roadmap of activities to that situation. Anyway, thank you very much for your great support.

[Jenefer-Monroe]

You are so welcome. Please do come to our office hours if you are able, we'll be sharing about our roadmap as this work comes so you can be aware of it, and of course answering your questions.

Join the CoE Starter Kit Office hours held on the second Wednesday of each month between 7:00AM and 8:00AM Pacific time. Click here to download the calendar file to add to your calendar: Calendar file

[Jenefer-Monroe]

I'll go ahead and close this then. Thanks again for using CoE