User Profile
APQueiroz
Frequent Visitor
Joined 3 years ago
User Widgets
Contributions
Re: Error fetching data for this visual - Desktop working, service error
Hello gck02, I encountered a similar issue recently. I was using an on-premises gateway with ODBC to connect to an on-premises database, but I kept getting an error message and couldn't resolve it despite numerous attempts. As a partial solution, I switched to using a personal gateway, which allowed the dataset to update without any problems. Have you tried this method?7KViews0likes1CommentRe: Joing tables with conditions using Power Query is not working as it should.
Thank you greatly, AlienSx . That solved my problem—ingenious! It never crossed my mind to use lists like that in Power Query; I thought it would be slower than using tables. It’s perfect for custom unpivot and merging large timeline tables! The only issue is that the column used for sorting cannot contain null values. In my case, I replaced the nulls with a value between the minimum and maximum values.836Views0likes0CommentsJoing tables with conditions using Power Query is not working as it should.
Hello, experts! I hope you can help me with this issue. Please excuse any language mistakes. I am working with two tables in Power Query. The first table contains assignment activity data for equipment, with around 80,000 rows. The second table holds telemetric data for the same equipment, with over 3 million rows. Assignment data example equipment_id(Int64) start_date(Datetime) end_date(DateTime) assignment_id(Int64) 141231234 2024-10-01 00:00 2024-10-01 01:00 34453 357463345 2024-10-01 01:00 2024-10-01 01:40 23423 317134523 2024-10-01 01:40 2024-10-01 03:00 62342 Telemetric data example equipment_id(Int64) timestamp(Datetime) speed 357463345 2024-10-01 00:05 10 357463345 2024-10-01 00:10 8.2 317134523 2024-10-01 00:15 5.6 My goal is to match each telemetric record with its corresponding assignment_id based on the timestamp interval, allowing me to establish a 1:n relationship between the two tables and calculate the necessary measures. Here’s my current approach. I created a calculated column as follows: let AddReferenceColumn = Table.AddColumn( telemetric_data, "assignment_id", (row) => let search = Table.SelectRows( assignment_data, each ([equipment_id] = row[equipment_id] and [start_date] <= row[timestamp] and [end_date] >= row[timestamp]) ) in if Table.RowCount(search) > 0 then search{0}[assignment_id] else null ) in AddReferenceColumn This solution works as expected on a sample of 1,000 rows, taking around 4 minutes to load the preview. However, when I attempt to load the full dataset, it becomes extremely slow and never completes. I then tried a different approach: let Source = Table.Join(telemetric_data, "equipment_id", assignment_data, "equipment_id"), FilterRows = Table.SelectRows(Source, each [timestamp] >= [start_date] and [timestamp] < [end_date]) in FilterRows This also worked but still failed to load the entire dataset. What would be the best way to solve this problem?Solved971Views0likes4CommentsRe: Function give me DataflowContainsDynamicDatasource on DataFlow, why?
Hello v-yueyunzh-msft ! It's embarrassing that Microsoft hasn't implemented this feature yet. This impairs, on an immeasurable scale, the versatility of developments. I hope it gets done as soon as possible. Unfortunately, this dynamic connection is crucial for me because I need to connect to multiple databases, and I cannot afford to get it done manually with parameters alone. Thank you so much for the response to my question! I hope you have a wonderful day, Allan Pimentel875Views0likes0CommentsRe: Adding New Rows Automatically from Loaded Table into New Custom Table
Hello! Sorry if my English is bad. I'm still learning. Considering that you can't have cyclic references in power query, you can create a column in Table A that identifies the cycle of the cost, then summarize the Cost value in the Pareto table. If my cycle, like in your example, is the week of the year, I can do the following: Generating a example for table A let #"Generated Series" = List.Generate( () => [ Date = DateTime.LocalNow(), Cost = Number.RandomBetween(10, 10000) ], each [Date] > DateTime.LocalNow() - #duration(90, 0, 0, 0), each [ Date = [Date] - #duration(1, 0, 0, 0), Cost = Number.RandomBetween(10, 10000) ], each _ ), #"Generated table" = Table.TransformColumnTypes( Table.FromRecords(#"Generated Series"), {{"Date", type date}, {"Cost", type number}} ), #"Add year week" = Table.AddColumn(#"Generated table", "Week", each Date.WeekOfYear([Date]), Int64.Type) in #"Add year week" Now, I can simply summarize the Cost per week in the Pareto table: let SummarizedData = Table.Group( #"Table A", "Week", {"Total", each List.Sum([Cost])} ), #"Change Type" = Table.TransformColumnTypes(SummarizedData,{{"Total", type number}}) in #"Change Type" And there you have it! I hope that helps.574Views0likes0CommentsFunction give me DataflowContainsDynamicDatasource on DataFlow, why?
Hello guys. This is my first post in the community. Forgive me if I made any mistakes in this post. I was exploring different ways to connect to a Postgres DB. That when I create the following function ( Host as text, Database as text, ViewName as text ) as table => let Connection = PostgreSQL.Database(Host, Database), ViewData = Connection{[Schema = "myschema", Item = ViewName]}[Data] in ViewData It works as intended on the PBI Desktop, but when I try to put it on a Dataflow, it gives me the error DataflowContainsDynamicDatasource ErrorMessageOne or more entities references a dynamic data source. Status code: 400 Error code: DataflowContainsDynamicDatasource Why does this function make this error appear? Is there a way around this problem?Solved934Views0likes2Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.