Power Query - update particular columns

hi,

I need to creat quite complex table with Power Query. That final table has many (up to 40) columns. The problem is that I need to place few extra columns with advance formulas into the final table (after Power Query). Those columns are not next to each other.


Is there any way to refresh that table without loosing those extra columns with formulas?

an exaple:

PQ source column	PQ source column	extra column, added ater PQ, with formula	PQ source column	extra column, added ater PQ, with formula	PQ source column	PQ source column

1. Please update your profile to show the Excel version you are using.

2. Why are you adding formulas after Power Query? Why not add those within your query? If you don't know how, then perhaps we can help. Attach a sample workbook, showing your query, and the formulas you are adding.

It should be simple enough to replace those worksheet formulas within your Query. But your attachment has no query in it - how can I edit your query?!

So add these lines to your query, to replace the worksheet formulas, and reorder the columns:

let
    Źródło = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Klient", type text}, {"Invoice Date", type date}, {"Item Description", type text}, {"Product Line", type text}, {"Product Type Description", type text}, {"price", Currency.Type}, {"number of pcs", Int64.Type}, {"Bottem Gauge", type text}, {"Body Gauge", type text}, {"Top Gauge", type text}, {"Customer Item Number", type text}}),
    #"Added Revenue" = Table.AddColumn(#"Zmieniono typ", "Total Revenue", each [price] * [number of pcs], Currency.Type),
    GaugeText = (GaugeInput) =>
        let
            #"Replace 0" = Text.Replace(GaugeInput,"0",""),
            #"Replace ." = Text.Replace(#"Replace 0",".",""),
            #"Replace MM" = Text.Replace(#"Replace ."," MM",""),
            #"Replace Second Choice" = Text.Replace(#"Replace MM","SECOND CHOICE",""),
            #"Replace 12 with 2" = Text.Replace(#"Replace Second Choice","12","2")
        in
            #"Replace 12 with 2",
    #"Added Barrel Description" = Table.AddColumn(#"Added Revenue", "Opis beczki", each if [Product Line] <> "Large Steel Drums>=44G/160ltr+" then null else Text.Combine({Text.Combine(List.ReplaceMatchingItems({Text.Start([Item Description],2)},{{"OL","OH"},{"RL","OH"},{"RH","OH"},{"TC","TH"}})),GaugeText([Top Gauge]) & GaugeText([Body Gauge]) & GaugeText([Bottem Gauge]),if Text.Middle([Item Description],22,1) = "L" then " IC" else null}," "), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Barrel Description",{"Klient", "Invoice Date", "Item Description", "Product Line", "Product Type Description", "price", "number of pcs", "Total Revenue", "Bottem Gauge", "Body Gauge", "Top Gauge", "Opis beczki", "Customer Item Number"})
in
    #"Reordered Columns"

There's no magic translator. Just step through what the formula is actually doing, and replicate the function in Power Query.

            #"Replace 0" = Text.Replace(GaugeInput,"0",""),
            #"Replace ." = Text.Replace(#"Replace 0",".",""),
            #"Replace MM" = Text.Replace(#"Replace ."," MM",""),
            #"Replace Second Choice" = Text.Replace(#"Replace MM","SECOND CHOICE","")

I like to combine multiple replace with null/blank with Splitter.SplitByAnyDelimiter()
Ex:

Text.Combine(Splitter.SplitTextByAnyDelimiter({"0","."," ","MM","SECOND CHOICE"})(GaugeInput))

Edit: Woops, forgot closing parenthesis.

It's a custom function. You can see the steps it performs in the code.