+ Reply to Thread
Results 1 to 11 of 11

Power Query - update particular columns

Hybrid View

  1. #1
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query - update particular columns

    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"
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  2. #2
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: Power Query - update particular columns

    wow, thanks.

    is there any easy way of translating excel formulas to PQ, as you did above in that line: #"Added Barrel Description" = Table.AddC......

  3. #3
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: Power Query - update particular columns

    hi ,

    I have one more question. How did you make GaugeText step? Where in PQ can I find it?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select sheet after Power Query Update
    By jonrack in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2019, 02:16 PM
  2. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  3. [SOLVED] Power Query ranking multiple columns
    By rs1aj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-26-2019, 11:43 PM
  4. Update pivot tables without refreshing power query
    By Acceptablerisk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-25-2019, 04:48 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. Power Query Won't Update
    By Justair07 in forum Excel General
    Replies: 9
    Last Post: 09-29-2016, 04:46 PM
  7. automate power query update from new tab (office excel 2016)
    By ajnuna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2016, 07:27 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1