Add manual columns to power query without losing data on refresh

[timcpugh]

Hello there, i've created the following power query however when i add columns with manual data, then refresh- i lose this data/or it moves to the wrong line. What changes are required to avoid this?

Current query:

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Delegate System", type text}, {"Item No.", type text}, {"Item", type text}, {"Vendor Group", type text}, {"Vendor", type text}, {"Over Group", type text}, {"Major Group", type text}, {"Item Group", type text}, {"Cost Center/Store", type text}, {"Unit", type text}, {"ANNUAL QTY/UNIT", Int64.Type}, {"MANUFACTURER", type any}, {"REPLACE/KEEP  COMMENTS", type any}, {"STATE", type text}, {"Abco Code", Int64.Type}, {"Desc", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Delegate System", "Item", "Vendor Group", "Vendor", "Over Group", "Major Group", "Item Group", "Cost Center/Store", "Unit", "REPLACE/KEEP  COMMENTS", "STATE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Abco Code", "Desc", "Item No.", "ANNUAL QTY/UNIT", "MANUFACTURER"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"MANUFACTURER"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Abco Code", "Desc", "ANNUAL QTY/UNIT", "Item No."}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Abco Code", "Desc"}, {{"Total_Qty", each List.Sum([#"ANNUAL QTY/UNIT"]), type nullable number}, {"Old_Codes", each Text.Combine(List.Distinct([#"Item No."]), ", "), type text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total_Qty", Order.Descending}})
in
    #"Sorted Rows"

[hamster]

post example of your anonymised source data , your table with additional columns and expected result
or attach excel file with these examples

[hamster]

you can try this

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TSC = Table.SelectColumns(Source,{"Abco Code", "Desc", "ANNUAL QTY/UNIT", "Item No."}),
    Group = Table.Group(TSC, {"Abco Code", "Desc"}, {{"Total_Qty", each List.Sum([#"ANNUAL QTY/UNIT"]), type nullable number}, {"Old_Codes", each Text.Combine(List.Distinct([#"Item No."]), ", "), type text}}),
    Type = Table.TransformColumnTypes(Group,{{"Abco Code", Int64.Type}, {"Desc", type text}, {"Total_Qty", Int64.Type}, {"Old_Codes", type text}}),
    Sort = Table.Sort(Type,{{"Total_Qty", Order.Descending}})
in
    Sort

but if it doesn't work see my previous post

[HansV]

Welcome to Eileen's Lounge and thanks for helping out!

[hamster]

@HansV

[timcpugh]

post example of your anonymised source data , your table with additional columns and expected result
or attach excel file with these examples

See the example attached, and video to replicate the issue: https://www.dropbox.com/s/lcmx6xzps4itp ... 6.mp4?dl=0

[hamster]

this is completely different from what you wrote in the first post
so see this: https://exceleratorbi.com.au/self-refer ... wer-query/