Move Dataflow Tort Vision for Clients to New Clients Reports workspace

[jesusitd]

Task:

Copy Dataflow and entities to new workspace

Set up basic Scheduled Refresh

Set up advanced refresh with pythonpowerbirefresher

Modify ALL FIRMS DATASETS to point to this dataflow instead

[jesusitd]

Created M Code to grab all Dashlets in CRM containing a Power BI Link.

let Source = MySQL.Database("slave1.sql.intakedesk.com", "suitecrm", [ReturnSingleDatabase=true, OldGuids = true, Query = "SELECT assigned_user_id , user_name , first_name , last_name , CONVERT(FROM_BASE64(contents) USING utf8) AS contents_decoded FROM user_preferences LEFT JOIN users ON user_preferences.assigned_user_id = users.id WHERE category = 'Home' AND FROM_BASE64(contents) LIKE '%https://app.powerbi.com%'"]),

"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"contents_decoded", Splitter.SplitTextByDelimiter("https", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "contents_decoded"),

#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"contents_decoded", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"contents_decoded", Splitter.SplitTextByDelimiter("https", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "contents_decoded"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each Text.StartsWith([contents_decoded], "://app.powerbi.com")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"contents_decoded", type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type1", {{"contents_decoded", each Text.BeforeDelimiter(_, ";"), type text}}),
#"Added Prefix" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"contents_decoded", each "https" & _, type text}}),
#"Removed Duplicates" = Table.Distinct(#"Added Prefix", {"assigned_user_id", "contents_decoded"})

in

"Removed Duplicates"

[jesusitd]

Waiting on approval from HK CC @richarditd to migrate these reports.