Forum Discussion
Default query when opening Power Query?
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
The file is auto opening on Hormel because your target query is part of an active dependency chain (you show Source = Table.Combine({Hormel, #"Not Hormel"}) and several Table.NestedJoin steps).
Make a truly independent landing query. Either create a topmost Landing query with no references or duplicate the Sales history query and inline the actual source steps from Hormel and Not Hormel into the duplicate (rename those local blocks and set Source = Table.Combine({Source_Hormel, Source_NotHormel})) so the duplicate has no incoming arrows in View -> Query Dependencies. Save the PBIX, fully close Power BI and reopen, the editor will then open to that independent query.
Hi,
As far as I know, there is no way to set a default query for Transform data.
In your case Sales history depends on Hormel and Not Hormel, so Power Query opens the first query in that chain (Hormel).
A simple workaround is to create a new query that just references Sales history and move it to the top of the Queries list, or right-click Sales history in the Fields pane and choose Edit query to open it directly.
Hi - the issue still persists. Here is the Advanced Query code:
let
Source = Table.Combine({Hormel, #"Not Hormel"}),
#"Filtered Rows" = Table.SelectRows(Source, each ([EDAYEAR] <> "#(001A)")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"HEADQUARTERSNBR"}, #"Member HQ", {"Member #"}, "Member HQ", JoinKind.LeftOuter),
#"Expanded Member HQ" = Table.ExpandTableColumn(#"Merged Queries", "Member HQ", {"Member city", "Member state", "Member ZIP", "Legacy member", "Member name fixed", "Smithfield NESP", "Regional"}, {"Member city", "Member state", "Member ZIP", "Legacy member", "Member name fixed", "Smithfield NESP", "Regional"}),
#"Added Custom" = Table.AddColumn(#"Expanded Member HQ", "Brand owner", each if [BRAND]="Packer Label" then "Packer" else "UniPro"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Brand owner", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"EDAYEAR", "Year"}, {"EDAMNTH", "Month"}, {"HEADQUARTERSNBR", "Member #"}, {"DEPTCODE", "Department #"}, {"DEPTNAME", "Department name"}, {"BRAND", "Brand"}, {"Category Family Name", "Family Name"}, {"Category Line Name", "Line Name"}, {"Category Category Name", "Category Name"}, {"Category Group Name", "Group Name"}, {"PRODUCTUPC", "UPC"}, {"GTIN_DESCRIPTION", "Description"}, {"EDA_CY", "EDA"}, {"SPA_CY", "SPA"}, {"PURCH_ALL_CY", "COGS"}, {"CASES_ALL_CY", "Cases"}, {"LBS_ALL_CY", "Tonnage"}, {"Kosher", "Is Kosher"}, {"Is Halal Certified", "Is Halal"}, {"Status", "Partners Plus Status"}, {"Level", "Partners Plus Level"}, {"Rancher's Legacy", "Rancher's Legacy item"}, {"Season", "Conference Season"}, {"Attended?", "Conference attendee"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"HEADQUARTERSNAME", "Member Name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Member name fixed", "Member Name"}, {"Department name", "Department Name"}, {"Rancher's Legacy item", "Rancher's Legacy Item"}, {"Conference attendee", "Conference Attendee"}, {"Member city", "Member City"}, {"Member state", "Member State"}, {"Legacy member", "Legacy Member"}, {"Brand owner", "Brand Owner"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Year", "Month", "Supplier Number", "Supplier Name", "Member #", "Member Name", "Member Status", "Department #", "Department Name", "Region Name", "GTIN", "Brand", "Family Name", "Line Name", "Category Name", "Group Name", "UPC", "Description", "EDA", "SPA", "COGS", "Cases", "Tonnage", "Storage State", "Is Halal", "Is Kosher", "Partners Plus Status", "Partners Plus Level", "EB Item Available", "Rancher's Legacy Item", "Conference Season", "Conference Attendee", "Member City", "Member State", "Member ZIP", "Legacy Member", "Brand Owner"}),
#"Inserted Addition" = Table.AddColumn(#"Reordered Columns", "EDA+SPA", each [EDA] + [SPA], type number),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Addition",{{"Year", Int64.Type}, {"Month", Int64.Type}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "Supplier # & Name", each Text.Combine({[Supplier Number], [Supplier Name]}, "-"), type text),
#"Changed Type2" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Supplier Number", Int64.Type}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type2", {"Supplier Number"}, #"Integrated vendors", {"Number"}, "Integrated vendors", JoinKind.LeftOuter),
#"Expanded Integrated vendors" = Table.ExpandTableColumn(#"Merged Queries1", "Integrated vendors", {"Main vendor"}, {"Main vendor"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Integrated vendors",{{"Main vendor", "Main Supplier"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2",null,"N/A",Replacer.ReplaceValue,{"Main Supplier"}),
#"Renamed Columns3" = Table.RenameColumns(#"Replaced Value",{{"Normalized", "Normalized name"}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Renamed Columns3",{{"Normalized name", Text.Proper, type text}}),
#"Renamed Columns4" = Table.RenameColumns(#"Capitalized Each Word",{{"Normalized name", "Normalized Supplier Name"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns4","Unknown","Unk",Replacer.ReplaceText,{"Description"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"Not Provided",Replacer.ReplaceValue,{"Storage State"}),
#"Inserted Merged Column1" = Table.AddColumn(#"Replaced Value2", "Member name & number", each Text.Combine({Text.From([#"Member #"], "en-US"), [Member Name]}, "-"), type text),
#"Added Custom1" = Table.AddColumn(#"Inserted Merged Column1", "Month name", each Date.MonthName([Date])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Month name", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type3", each [#"Member #"] <> -1),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows1",{"Year", "Month", "Month name", "Supplier Number", "Supplier Name", "Member #", "Member Name", "Member Status", "Department #", "Department Name", "Region Name", "GTIN", "Brand", "Family Name", "Line Name", "Category Name", "Group Name", "UPC", "Description", "EDA", "SPA", "COGS", "MDM Pack Size", "Manufacturer", "Mfr Product #", "Cases", "Tonnage", "Normalized Supplier Name", "Storage State", "Is Halal", "Is Kosher", "Category", "Partners Plus Status", "Partners Plus Level", "EB Item Available", "Date", "Rancher's Legacy Item", "Conference Season", "Conference Attendee", "Member City", "Member State", "Member ZIP", "Legacy Member", "Brand Owner", "EDA+SPA", "Supplier # & Name", "Main Supplier", "Member name & number"}),
#"Renamed Columns5" = Table.RenameColumns(#"Reordered Columns1",{{"Month", "Month #"}}),
// NEW: normalize & convert whitespace-only to null across all text columns
#"Fix Whitespace-To-Null" =
let
CleanNull = (s as nullable text) as nullable text =>
let
s1 = if s = null then null else Text.Replace(s, Character.FromNumber(160), " "), // NBSP -> space
s2 = if s1 = null then null else Text.Clean(s1),
s3 = if s2 = null then null else Text.Trim(s2)
in
if s3 = null or s3 = "" then null else s3,
Prev = #"Renamed Columns5",
// Get text columns by sampling the first row (safe for mixed types)
SampleRow = try Prev{0} otherwise null,
TextCols =
if SampleRow = null then {}
else List.Select(Table.ColumnNames(Prev),
(c) => Type.Is(Value.Type(Record.Field(SampleRow, c)), type text)),
Result = Table.TransformColumns(Prev, List.Transform(TextCols, each {_, CleanNull, type text}))
in
Result,
#"Merged Queries2" = Table.NestedJoin(#"Fix Whitespace-To-Null", {"Department Name"}, #"Dept binning", {"Department name"}, "Dept binning", JoinKind.LeftOuter),
#"Expanded Dept binning" = Table.ExpandTableColumn(#"Merged Queries2", "Dept binning", {"Dept Bin"}, {"Dept Bin"})
in
#"Expanded Dept binning"
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
The file is auto opening on Hormel because your target query is part of an active dependency chain (you show Source = Table.Combine({Hormel, #"Not Hormel"}) and several Table.NestedJoin steps).
Make a truly independent landing query. Either create a topmost Landing query with no references or duplicate the Sales history query and inline the actual source steps from Hormel and Not Hormel into the duplicate (rename those local blocks and set Source = Table.Combine({Source_Hormel, Source_NotHormel})) so the duplicate has no incoming arrows in View -> Query Dependencies. Save the PBIX, fully close Power BI and reopen, the editor will then open to that independent query.