Query Editor contains data not in table data view (and more...)

[NotMyJob]

Hey All,

I'm very new to PBI, and the learning curve has been steep...  that said I have two problems with messing with both the data file and the PBI file... 

I duplicated a column in the query editor, and now can't get rid of it without having it listed as an Applied Step.  And also, the duplication step isn't listed in the Applied Step list.  There's more problems in that column, but if I can remove the column and having that action not listed in the Applied Step list, I'll be happy.

Second, in the source .xlsx file there are 15 rows that are empty in a column, and the rest show dates.  I refreshed the data and the old data still shows in the Query Editor but NOT in the Data view!  This is just maddening....

Any ideas, suggestions?

Thanks.  I'm in a udemy course for this now, have learned a lot, but I know I'm just scratching the surface...

[v-yiruan-msft · Accepted solution]

Hi  @NotMyJob ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[HotChilli]

Apologies for the delay in responding (I didn't get a notification - which is unusual).

Alright so I'm trying to get to the bottom of this "I duplicated a column in the query editor, and now can't get rid of it without having it listed as an Applied Step.  And also, the duplication step isn't listed in the Applied Step list.  There's more problems in that column, but if I can remove the column and having that action not listed in the Applied Step list, I'll be happy."

So what I can see in the code is the duplication of column "Completion Date" then some reordering, then the column gets renamed("Completion Year"), then some values get changed and then the column gets deleted.  That seems like a lot of work for no reason. 

You say that the duplication step is not visible in the Applied Steps.  Hmmm, that would be unusual.  If you've found it, then just remove the step (there are options on the right-click if you want to delete all steps after it too)

If you feel brave enough, you can edit the above code in the Advanced Editor to remove the lines you don't want.  If it doesn't pass a syntax check, it will tell you.

---

For the second problem, you have to Close and Apply in Power Query to get the data updated.

Let me know how you get on

[NotMyJob]

I should have kept track of the steps, however the best I can say is that I removed the Applied Steps, Closed and Applied.  Refresh Data tossed errors again.  I Saved, Refreshed Data, and still the errors.  I then closed PBI and re-opened it and Refreshed Data whereupon it did not toss errors, I manually checked the data calculations and it all is good.

Terribly frustrating on my part to have to close an application and re-open it for changes to work.  I'm on the July 2022 version, so that should all be good as well...  (Version: 2.107.841.0 64-bit (July 2022))

Thanks for the replies!  Everything adds to the KB in this muddled head of mine  🙂

[v-yiruan-msft]

Hi @NotMyJob ,

Please save the applied codes in Advanced Editor and delete the related query in Power Query Editor. Then connect to the data source again, and paste the codes for the left steps in Advanced Editor. Later check if the data can be loaded into model side and can be seen in data view...

Data Not Showing Up

Hello, thank you for checking in. I tried removing and importing new datasets, and for some reason that fixed the problem. I had tried refreshing and closing and opening the document, but neither of those worked, so I just re-imported the datasets and that fixed it. It was weird, but it is working now.

Data in Query Editor and Table View but not in Report

Update: without changing anything in the original pbix, it is working today. The ONLY thing that's different from yesterday on my side is that I rebooted my PC. I don't see, how this would make a difference, though.

If the above one is not working, could you please provide some fake data in the sheet "% Complete" and full applied steps in Advanced Editor in order to make troubleshooting and give you a suitable solution? By the way, the data source is the excel file from Sharpoint? Thank you.

Best Regards

[NotMyJob]

This is suspected as being a network permissions issue as I'd escalated the problem and a week later PBI is behaving as expected.  Or... I did something and it fixed the problem.  Too many fires to keep track of how it all worked out, unfortunately.  

Thank you for the replies and suggestions!

[v-yiruan-msft]

Hi @NotMyJob ,

It's glad to hear that it works. And thanks for your feedback and sharing the problem situation. Could you please mark your post as Answered? Thank you.

Best Regards

[HotChilli]

Post the Advanced Editor code from Power Query and I'll have a look

[NotMyJob]

Hoping this is what you're asking for 🙂

let
Source = Excel.Workbook(Web.Contents("https://umpquaholdings.sharepoint.com/sites/CybersecuritySecurityConsulting/Shared%2520Documents/Gen..."), null, true),
#"% Complete_Sheet" = Source{[Item="% Complete",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"% Complete_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Duplicated Column" = Table.DuplicateColumn(#"Promoted Headers", "Completion Date", "Completion Date - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Portfolio", "Initiative/Project Name", "Status", "Progress #(lf)(%) ", "Progress Delta", " Inherent Risk#(lf)(Initial)", " Residual Risk #(lf)(Point in Time)", "Pie Chart Flag", "Completion Date", "Completion Date - Copy", "Primary Drivers, Impediments, Blockers, etc."}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Completion Date - Copy", "Completion Year"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Completion Year", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","8/1/2021","2021",Replacer.ReplaceText,{"Completion Year"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Completion Year"})
in
#"Removed Columns"