Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Power BI Power Query edits not being applied

Hi, I have been having issues with my power query changes being applied. When I click the close and apply button in the editor, it will start to save, but it will stop during the process. Typically, it will stop when loading the rows from my SQL server import. It will reach around 300,000 rows from the database, and stop, then never complete the application of my changes.

 

while the table being imported has lots of data, I am only trying to use a very small portion of it, and in my power query I trim the table down to only 1000 rows. Any advice for fixing this?

4 REPLIES 4
lbendlin
Super User
Super User

needs more details - is this on Power BI desktop?  Is it a Dataflow?  Have you recently made meta data changes?

Anonymous
Not applicable

Hi, sorry about the late response, This is on the desktop app, it is a relativly simple transformation on a table using the following script

 

let
Source = Sql.Database("connectwise.database.windows.net", "ConnectWise"),
dbo_CwAuditTrail = Source{[Schema="dbo",Item="CwAuditTrail"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_CwAuditTrail,{"enteredBy"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([newStatus] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [auditText]),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Added Custom", {{"Custom", each Text.BetweenDelimiters(_, """", """"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Between Delimiters",{{"Custom", "Old Status"}, {"newStatus", "New Status"}, {"enteredDate", "Entered Date"}, {"auditText", "Audit Text"}, {"TicketId", "Ticket Id"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Entered Date", type datetime}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Ticket Id", "Ticket ID"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Ticket ID", Order.Descending}, {"Entered Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Index", "Duration", each if [Index]=0 then null else if [Ticket ID]=#"Sorted Rows"[Ticket ID]{[Index]-1} then [Entered Date]-#"Sorted Rows"[Entered Date]{[Index]-1} else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Duration", type duration}})
in
#"Changed Type1"

 

That is really the only thing I have done in power BI

The sorting and the pointer math are likely what's killing it. 

I have recently seen a clever approach - after sorting, make a copy of the table containing only the entered date, then add a zero based index to one table and a one based index to the other.  When you merge the tables back together you get the pointer math for free.  Might want to try that.

Anonymous
Not applicable

I've confirmed it is at the very least not the sorting, as if I load the step before the pointer math, it load fairly quickly. I will see what I can do about trying out the method you gave me with the math and see if that helps. However, I don't believe this will fix the main issue. Currently, when I try to apply the changes, Power BI will not finish loading the database, and will eventually get stuck here:

FishKing_0-1660072032103.png

 

Except it will be around the 300,000 mark.

 

Edit, it looks like that was wrong, as when I tried deleting the section that does the pointer math, it successfully applied the changes.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.