Results 1 to 5 of 5

Help understanding Power Query Logic

Threaded View

Potholes Help understanding Power... 06-09-2019, 12:17 AM
AliGW Re: Help understanding "... 06-09-2019, 01:38 AM
AliGW Re: Help understanding Power... 06-09-2019, 02:03 AM
Potholes Re: Help understanding Power... 06-09-2019, 02:19 AM
AliGW Re: Help understanding Power... 06-09-2019, 02:29 AM
  1. #1
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Help understanding Power Query Logic

    Good afternoon all

    Below is some of my PQ coding, down towards the bottom I have just for this purpose moved some lines down,what I am experimenting
    with is to remove the information from the " nMemonic " column off the csv, that has come in on the main database and remove it from displaying
    in the end product. I can see where to remove it below code

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Call", "Output", " It Was Here", "Input","Location", "Service Area", "Latitude", "Longitude", "Maidenhead", "S", "ERP", "HASL", "T/O", "Sp", "Tone", "Wavelength", "Technology", "Group"})

    
    let
        URL = "http://www.wia.org.au/members/repeaters/data/",
        WebSource = Table.FromColumns({Lines.FromBinary(Web.Contents(URL))}),
        FileSource = Text.Combine({URL,Text.BeforeDelimiter(Text.AfterDelimiter(Table.SelectRows(WebSource, each Text.Contains([Column1], ".csv")){0}[Column1],"href="""),".csv"),".csv"}),
        Source = Csv.Document(Web.Contents(FileSource),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Replaced nulls" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
        #"Replaced hyphens" = Table.ReplaceValue(#"Replaced nulls","-",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
        #"Promoted Headers" = Table.PromoteHeaders(#"Replaced hyphens", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Output", type number}, {"Input", type number}, {"Latitude", type number}, {"Longitude", type number}, {"ERP", type number}, {"HASL", type number}, {"T/O", type number}, {"Tone", type number}}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", List.Transform(Table.ColumnNames(#"Changed Type"), each {_, null})),
        #"Added Maidenhead" = Table.AddColumn(#"Replaced Errors", "Maidenhead", each Maidenhead([Latitude], [Longitude]), type text),
        #"Added Criteria" = Table.AddColumn(#"Added Maidenhead", "Criteria", each Text.BetweenDelimiters([Notes], "{{", "}}"), type text),
        #"Replaced Null" = Table.ReplaceValue(#"Added Criteria","",null,Replacer.ReplaceValue,{"Criteria"}),
        #"Filled Down" = Table.FillDown(#"Replaced Null",{"Criteria"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Criteria", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Wavelength", "Technology"}),
        #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Call] <> null and [Call] <> ""),
        #"Added Group" = Table.AddColumn(#"Filtered Rows", "Group", each if Text.Contains(Text.Upper([Service Area]), "WICEN") then "Wicen" else if [Wavelength] = "23CM" then "VK23cm" else if [Technology] = "FM" then Text.Start([Call],3) else "VK" & [Technology], type text),
        
    
       #"Removed Columns" = Table.RemoveColumns(#"Added Group",{"Notes"}),
        
       #"Removed Columns" = Table.RemoveColumns(#"Added ",{"nMemonic"}),    <<<<<<<<<<<<<<<<<<<<<<  Added Instruction for removal  
        
       #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Call", "Output", "Input","Location", "Service Area", "Latitude", "Longitude", "Maidenhead", "S", "ERP", "HASL", "T/O", "Sp", "Tone", "Wavelength", "Technology", "Group"})
    in
        #"Reordered Columns"
    
    
    Peter
    Last edited by AliGW; 06-09-2019 at 01:40 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 12-05-2018, 07:59 AM
  2. VBA Error handling for "Send a frown" [Power Pivot/Power BI]
    By Kayees in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-11-2018, 03:25 PM
  3. Replies: 0
    Last Post: 06-22-2018, 09:46 PM
  4. Replies: 0
    Last Post: 05-30-2017, 04:48 PM
  5. Need help understanding "Index" and "Match"
    By joannelittell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2013, 03:08 PM
  6. [SOLVED] Need help understanding "Index" and "Match"
    By joannelittell in forum Excel General
    Replies: 2
    Last Post: 10-29-2013, 02:55 PM
  7. Defining logic "Yes" or "No" , based on multiple conditions
    By pyol17 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 02:41 AM

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