Forum Discussion

NotMyJob's avatar
NotMyJob
Helper III
4 years ago
Solved

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

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 qu...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
HotChilli
Community Champion
4 years ago

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

NotMyJob's avatar
NotMyJob
Helper III
4 years ago

Hoping this is what you're asking for 🙂

let
Source = Excel.Workbook(Web.Contents("https://umpquaholdings.sharepoint.com/sites/CybersecuritySecurityConsulting/Shared%2520Documents/General/Security%2520Consulting%2520Dashboards%2520%2526%2520Reporting/Dashboard%2520Workbooks/Dashboard%2520Charts.xlsx"), 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"