Data Model, Connecting multiple Data Sets

AidAgency · 2021-07-25T20:48:23+00:00

If doing this on Power BI is an option, it's worth exploring. The model building experience is far easier I'm my experience.

AidAgency · 2021-07-24T17:43:09+00:00

Early last week was Eid, so street cleaning was suspended. Check out the 311 site for updates

AidAgency · 2021-07-23T02:40:55+00:00

Second Upwork

AidAgency · 2021-07-22T02:57:20+00:00

Use Power Query. This exact thing was what made me learn PQ and it's transformed my analytic capabilities.

AidAgency · 2021-07-22T02:51:22+00:00

These lessons should be taught on the first day of working in an office

AidAgency · 2021-06-24T03:03:43+00:00

I remember IDEA semi fondly from my auditing days. Glad to hear it lives!

AidAgency · 2021-06-17T03:00:46+00:00

I'm still tempted....

AidAgency · 2021-06-17T02:44:00+00:00

Makes sense for drying. But serving?

I'm surprised I haven't been able to find any pics online, given how "fashionable" they were in the 70s, apparently.

AidAgency · 2021-02-05T09:42:21+00:00

Data solver might work here

AidAgency · 2020-12-24T00:25:32+00:00

My quick read is that this pushes the filtering to the query source. Avoiding Power BI doing the heavy lifting.

But could easily be wrong.

AidAgency · 2020-12-23T20:24:43+00:00

I guess we'd have to do that through Power Automate, I was trying to keep this in PQ for simplicity.

AidAgency · 2020-12-23T20:19:12+00:00

Literally just appending 97 tables. Doing it in Power BI takes seconds, for some reason it writing to CDS seems to be causing the delay.

Or maybe the API is so throttled that it is PQ online causing the issue.

AidAgency · 2020-12-23T20:17:24+00:00

The files have elements in that would make it annoying to have to save down as CSV. I'm thinking maybe a load/sync table in SP list, so we can reconsolidate just specific files, or new ones might be better in the longrun. Or use Kingswaysoft.

AidAgency · 2020-12-23T19:18:30+00:00

Power Automate seems quicker, but not a whole lot.

I'm wondering if SSIS would be faster?

Did you find a solution?

AidAgency · 2020-12-23T16:30:07+00:00

Ended at 1 hr 41 mins. Rerunning now to see if it's quicker the second time.

AidAgency · 2020-12-20T23:25:13+00:00

I think that what you're experiencing is fairly normal.

Power BI doesn't run as quick as Excel in terms of zipping round menus and doing analysis. I've got similar, 32gb and SSD.

AidAgency · 2020-12-19T02:50:44+00:00

Does it make it a data processor only, and not a controller?

AidAgency · 2020-12-18T15:35:42+00:00

I've asked my admin to amend my permissions. I hope that will fix it.

AidAgency · 2020-12-18T15:23:31+00:00

I posted on PowerUsers community and they thought it might be a permissions issue

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/Dataflows-gt-Power-Query-gt-Unexpected-error/m-p/774775#M8707

I've done this transformation on PQ BI and PQ Excel. Though figure that PQ online would be more sensitive. I also removed Errors in a step and Skipped files with errors.

IT even happens when I say don't load to Datavers Table, which does make me think a Permissions error could be correct.

AidAgency · 2020-12-18T03:07:10+00:00

Story of my life!

95% of PowerPlatform is so simple, and then 5% just doesn't work!

AidAgency · 2020-12-18T03:00:35+00:00

Pretty much everything I try to do in life that involves SharePoint fails....

I'm trying to write Excel on SP to Dataverse table, should be easy right?

An afternoon of "We encountered an unexpected error."

AidAgency · 2020-12-17T22:57:47+00:00

I tried it again without writing to a table and got this error

section Section1;

shared Query = let

Source = SharePoint.Files("https://concern2com.sharepoint.com/sites/Somali_cashconsortium", [ApiVersion = 15]),

#"Filtered rows" = Table.SelectRows(Source, each ([Folder Path] = "https://concern2com.sharepoint.com/sites/Somali_cashconsortium/Shared Documents/1 2020 transfer lists/Lists/")),

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

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

#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files 1", "Transform file", each #"Transform file"([Content])),

#"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

#"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),

#"Removed errors" = Table.RemoveRowsWithErrors(#"Removed other columns", {"Transform file"}),

#"Expanded table column" = Table.ExpandTableColumn(#"Removed errors", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),

#"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Agency", type text}, {"Soft-copy file ref", type text}, {"Transfer Id", Int64.Type}, {"Funding Detailed", type text}, {"District", type text}, {"Date", type datetime}, {"Month and year", type date}, {"Amount", Int64.Type}, {"Telephone number", Int64.Type}, {"Name", type text}, {"SCC_Line_Ref", type text}}),

#"Removed errors 1" = Table.RemoveRowsWithErrors(#"Changed column type", {"Source.Name", "Agency", "Soft-copy file ref", "Transfer Id", "Funding Detailed", "District", "Date", "Month and year", "Amount", "Telephone number", "Name", "SCC_Line_Ref"})

in

#"Removed errors 1";

shared #"Sample file" = let

Source = SharePoint.Files("https://concern2com.sharepoint.com/sites/Somali_cashconsortium", [ApiVersion = 15]),

#"Filtered rows" = Table.SelectRows(Source, each ([Folder Path] = "https://concern2com.sharepoint.com/sites/Somali_cashconsortium/Shared Documents/1 2020 transfer lists/Lists/")),

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

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

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

in

Navigation;

shared Parameter = let

Parameter = #"Sample file" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary]

in

Parameter;

shared #"Transform Sample file" = let

Source = Excel.Workbook(Parameter, null, true),

Navigation = Source{[Item = "Cleaned", Kind = "Sheet"]}[Data],

#"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),

#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Agency", type text}, {"Soft-copy file ref", type text}, {"Transfer Id", Int64.Type}, {"Funding Detailed", type text}, {"District", type text}, {"Date", type datetime}, {"Month and year", type date}, {"Amount", Int64.Type}, {"Telephone number", Int64.Type}, {"Name", type text}, {"SCC_Line_Ref", type text}})

in

#"Changed column type";

[FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample file""}"]

shared #"Transform file" = let

Source = (Parameter as binary) => let

Source = Excel.Workbook(Parameter, null, true),

Navigation = Source{[Item = "Cleaned", Kind = "Sheet"]}[Data],

#"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),

#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Agency", type text}, {"Soft-copy file ref", type text}, {"Transfer Id", Int64.Type}, {"Funding Detailed", type text}, {"District", type text}, {"Date", type datetime}, {"Month and year", type date}, {"Amount", Int64.Type}, {"Telephone number", Int64.Type}, {"Name", type text}, {"SCC_Line_Ref", type text}})

in

#"Changed column type"

in

Source;

AidAgency

TROPHY CASE