[CoE Starter Kit - BUG] Dataflows not working properly - null users coming from Data Export

[nasilvae]

Does this bug already exist in our backlog?

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

Describe the issue

CoE BYODL Flows & CoE BYODL Makers Data flows are not working.

This is due to a mashup error related to the admin_maker entity.

Expected Behavior

Dataflows should run.

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?

BYODL DataFlows Maker/Flow

What method are you using to get inventory and telemetry?

Data Export

Steps To Reproduce

No response

Anything else?

Reference:
#7719

#7648

AB#2566

[Jenefer-Monroe]

Is this for the client we have in active email about? If so can we please schedule?

[nasilvae]

Is this for the client we have in active email about? If so can we please schedule?

Yes. Will send a poll in a moment. Thank you again.

[Jenefer-Monroe]

Worked with another customer and found that there were null creators getting sent through from flow data export file.
Here is a fix we are trying. Can you please try it and let me know if they still repro.

CoE BYODL Flows

Replace the entire content of the script in Advanced editor for Flows query with the below

let
    Source = AzureStorage.DataLake(DatalakeURL & "/powerautomate/Flows"),
      #"Changed modified" = Table.TransformColumnTypes(Source, {{"Date modified", type date}}),
  // Filter to files that have been updated since the last Dataflow refresh OR all files if initial inventory
  #"Filter to recent" = Table.SelectRows( #"Changed modified", each [Date modified] >= (if List.IsEmpty(DataflowRefresh) then Date.From("1900-01-01") else Date.From(DataflowRefresh{0}))),
    excludehiddenfiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    excludezerolengthfiles = Table.SelectRows(excludehiddenfiles, each [Attributes]?[Size]? > 0),
  // Extract Environment GUID from File Name
  #"Extract Environment ID from File Name" = Table.TransformColumnTypes(Table.AddColumn(excludezerolengthfiles, "EnvironmentName", each if Text.Length([Name]) = 41 then Text.Range([Name], 0,36) else Text.Range(Text.AfterDelimiter([Name], "-", 0), 0, 36)), {{"EnvironmentName", type text}}),
  // Remove unnecessary columns from the folder system, like extension and folder path.
  #"Removed unneeded columns" = Table.RemoveColumns(#"Extract Environment ID from File Name", {"Folder Path", "Attributes", "Date created", "Date modified", "Date accessed", "Extension", "Name"}),
  // Inner join of flow table with environments table, only keep flows where the environment still exists. This filters out flows from deleted environments.
  #"Merge with environments" = Table.NestedJoin(#"Removed unneeded columns", {"EnvironmentName"}, admin_environments, {"admin_environmentid"}, "admin_environments", JoinKind.Inner),
  #"Expanded Environments" = Table.ExpandTableColumn(#"Merge with environments", "admin_environments", {"admin_environmentid", "admin_displayname"}, {"admin_environmentid", "admin_environmentdisplayname"}),
  // Transform the content from the json files in the storage account into tables
  #"Get content from json files" = Table.AddColumn(#"Expanded Environments", "Transform file", each #"Transform file (9)"([Content])),
  // Expand all the columns the json file in the storage account provides into individual columns, e.g. flow name, flow id, created by, created on...
  #"Expanded columns" = Table.ExpandTableColumn(#"Get content from json files", "Transform file", {"resourceId", "name", "environmentId", "lifecycleState", "events_created_timestamp", "events_created_principalId", "events_modified_timestamp", "workflowEntityId", "flowSuspensionReason", "templateName"}, {"flowId", "name", "environmentId", "lifecycleState", "createdOn", "createdBy", "lastModifiedOn", "workflowEntityId", "flowSuspensionReason", "templateName"}),
  #"Removed errors" = Table.RemoveRowsWithErrors(#"Expanded columns", {"flowId"}),
  #"Removed Content binary column" = Table.RemoveColumns(#"Removed errors", {"Content"}),
  #"Remove null creators" = Table.SelectRows(#"Removed Content binary column", each [createdBy] <> null),
  // Merge flows with maker to get more maker details, such as maker display name, department, is orphaned etc for furhter logic (is the flow orphaned)
  #"Merged with makers" = Table.NestedJoin(#"Remove null creators", {"createdBy"}, admin_makers, {"admin_makerid"}, "admin_makers", JoinKind.LeftOuter),
  #"Expanded maker table" = Table.ExpandTableColumn(#"Merged with makers", "admin_makers", {"admin_makerid", "admin_userprincipalname", "admin_makerisorphaned", "admin_department", "admin_displayname", "admin_country", "admin_company", "admin_city"}, {"admin_makerid", "admin_userprincipalname", "admin_makerisorphaned", "admin_department", "admin_displayname", "admin_country", "admin_company", "admin_city"}),
  // Add utcNow timestamp which we use to identify when the dataflow last updated/created a row
  #"Added QueriedOn" = Table.AddColumn(#"Expanded maker table", "QueriedOn", each DateTimeZone.UtcNow()),
  // Add yes/no text field to identify if the flow is owned by a maker that no longer exists in the org
  #"Add IsOrphan" = Table.AddColumn(#"Added QueriedOn", "IsOrphanText", each if [admin_makerisorphaned] = true then "Yes" else "No"),
  #"Merged queries" = Table.NestedJoin(#"Add IsOrphan", {"flowId"}, admin_flows, {"admin_flowid"}, "admin_flows", JoinKind.LeftOuter),
  #"Expanded admin_flows" = Table.ExpandTableColumn(#"Merged queries", "admin_flows", {"admin_derivedownerdisplayname", "admin_derivedownermakerid"}, {"admin_derivedownerdisplayname", "admin_derivedownermakerid"}),
  #"Inserted conditional column" = Table.AddColumn(#"Expanded admin_flows", "DerivedOwnerFinal", each if [admin_derivedownermakerid] = null then [admin_makerid] else [admin_derivedownermakerid]),
  #"Add flowIsPublished" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted conditional column", "flowIsPublished", each true), {{"flowIsPublished", type logical}}) //to replace with logic once available
  ,
  #"Add isNotDeleted" = Table.TransformColumnTypes(Table.AddColumn(#"Add flowIsPublished", "markDeleted", each false), {{"markDeleted", type logical}}),
  #"Inserted conditional column 1" = Table.AddColumn(#"Add isNotDeleted", "markDeletedDate", each if [markDeleted] = false then null else [QueriedOn]),
  #"Changed column type" = Table.TransformColumnTypes(#"Inserted conditional column 1", {{"EnvironmentName", type text}, {"admin_environmentid", type text}, {"admin_environmentdisplayname", type text}, {"flowId", type text}, {"name", type text}, {"environmentId", type text}, {"lifecycleState", type text}, {"createdOn", type datetime}, {"createdBy", type text}, {"lastModifiedOn", type datetime}, {"admin_makerid", type text}, {"admin_userprincipalname", type text}, {"admin_makerisorphaned", type logical}, {"admin_department", type text}, {"admin_displayname", type text}, {"admin_country", type text}, {"admin_company", type text}, {"admin_city", type text}, {"QueriedOn", type datetimezone}, {"IsOrphanText", type text}, {"admin_derivedownerdisplayname", type text}, {"admin_derivedownermakerid", type text}, {"DerivedOwnerFinal", type text}, {"flowIsPublished", type logical}, {"markDeleted", type logical}, {"markDeletedDate", type datetimezone}})
in
    #"Changed column type"

CoE BYODL Makers

Replace the entire content of the script in Advanced editor for Combined Makers query with the below

let
  Source = Table.Combine({#"Environment Makers", #"App Makers", #"Flow Makers"}),
  #"Removed duplicates" = Table.Distinct(Source, {"createdBy"}),
  #"Remove nulls" = Table.SelectRows(#"Removed duplicates", each ([createdBy] <> null))
in
  #"Remove nulls"

[nasilvae]

Hey @Jenefer-Monroe, we noticed the following on the Makers Sync Cloud flow.

Is this intentional? We suspect that this is not letting the dataflows do a full sync due to the terminate breaking the cycle.

[Jenefer-Monroe]

Hello yes this is known please see #7790
Sorry I thought I had let you know about this in our email but it appears I did not.

[nasilvae]

Hello yes this is known please see #7790 Sorry I thought I had let you know about this in our email but it appears I did not.

gotcha no worries. I got confused I thought it was for the February release instead of march. Thank you again for the prompt support.

[Jenefer-Monroe]

My bad for shipping it :D

[Jenefer-Monroe]

Can you try something for me? I looked at the code a little more and I think this might be an issue of the refresh completions and the dataflows not running in the required order

1. Make the changes above to Maker and Flow dataflows

2. Confirm you only have “Next Refresh” for the Maker dataflow and not the others
   

3. Delete the Dataflow run history back a few weeks for all the Dataflows. You can do this in the Command Center
   

4. Manually refresh the Maker dataflow
   

5. Wait to see them run in order and if they pass.

Thank you so much!
Jenefer

[nasilvae]

Can you try something for me? I looked at the code a little more and I think this might be an issue of the refresh completions and the dataflows not running in the required order

1. Make the changes above to Maker and Flow dataflows
2. Confirm you only have “Next Refresh” for the Maker dataflow and not the others
   
3. Delete the Dataflow run history back a few weeks for all the Dataflows. You can do this in the Command Center
   
4. Manually refresh the Maker dataflow
   
5. Wait to see them run in order and if they pass.

Thank you so much! Jenefer

sounds like a plan. Will communicate with the team.

[Jenefer-Monroe]

The above with both the changes to Maker and Flow as well as the adjustment to be sure they run in order is working now for the other enormous org I'm working with so I'm hopeful.

[nasilvae]

The above with both the changes to Maker and Flow as well as the adjustment to be sure they run in order is working now for the other enormous org I'm working with so I'm hopeful.

We just implemented the changes. Now we are waiting for the dataflows to refresh.

[nasilvae]

The above with both the changes to Maker and Flow as well as the adjustment to be sure they run in order is working now for the other enormous org I'm working with so I'm hopeful.

We just implemented the changes. Now we are waiting for the dataflows to refresh.

Dataflows are working as of now. However orphan apps are not being tagged properly. We suspect because the orphaned flow is being throttled. #7889