Posts by alansidman

  • Multiple values per parameter using power query

    I am very confused by your explanation. You only have one record in your tables. there is no filtering to be done when there is only one record. I am unclear what you want to happen. Suggest you explain in simple English using your tables what you expect to do within Power Query.

    Quote

    When the value is entered under the Value field and refreshed it should fetch one or more ID details in the query tabs.

    To test this validity, you really need to have more than one record

  • Multiple values per parameter using power query

    Unable to read the entire Mcode as your source is from a SQL Database that I have no access to and the PQ editor is throwing up an error because it cannot see that DB. Perhaps you can transfer the source info to an excel sheet and make it part of the query so that we can see the whole deal.

  • A Little Access Help Please

    Your form is not linked (bound) to any Table. In order to store and calculate your data you need to have a table. A form is simply a means for loading data or reviewing the data. All calculations should be performed in Queries and if needed published in reports.


    Reading this whitepaper (attached), might help you to design your DB properly.

  • Remove non-numeric characters before first numeric character

    With Power Query, here is the Mcode


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2", "Column1.3"}),
    4. #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Column1.2", "Column1.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    5. #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.1"})
    6. in
    7. #"Removed Columns"


  • Conditional rearrangement of data

    Don't know what you are doing with the Mcode. This is universal and should work for 65000 rows. If you are unfamiliar with Power Query, then here is some additional information and links to help.


    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

  • Last 5 games scoring average

    Without a date or some other notation such as the Game number, I am not sure how you would determine the last 5 or last 10. What criteria would you use if you were doing this manually other than picking by sight.

  • Conditional rearrangement of data

    With Power Query, here is the Mcode that results in the following presentation shown in the attached file

    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    4. #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    5. #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Column2", "Column3"})
    6. in
    7. #"Removed Duplicates"

  • Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

    1. Your data is not presented consistently in your spreadsheet. Sometimes you have Test-1 and other times you have Test -1. Spacing is importing in the formattting of your data to get proper results.

    2. An alternative solution is using Power Query to split your data and then unpivot it.

    3. Once unpivoted, it is loaded into the Data Model (Power Pivot)

    4. In Power Pivot, create a new measure using concatenex (DAX feature)


    You can see it all in the attached file.

    Files

  • Reduce run time of macro

    For starters, I would add at the beginning


    Code
    1. Application.ScreenUpdating = False


    and before End Sub


    Code
    1. Application.ScreenUpdating = True

    additionally, you may wish to turn automatic calculations off

  • Transpose multiple columns whilst retaining other columns

    This can be quickly accomplished using power Query to unpivot the columns. Here is the Mcode


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    4. #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Entry ID", "Nominator Name", "Reason for nomination", "Nomination Type", "Value", "Status", "Award Amount", "Group size", "Date"}, "Attribute", "Value.1"),
    5. #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
    6. in
    7. #"Removed Columns"

    MCode

    select your range/table (blue)

    Data - From Table

    it will open Power Query Editor

    find and open Advanced Editor

    replace whole code there with code from the post

    be sure the name of the source table is the same as in the code (here: Table1)

    Done

    Close&Load


    Review PQ

    In the attached file

    Click on any cell in the new table

    On the Data Tab, click on Queries & Connections

    In the right window, double click to open Query

    Review PQ steps

    M-code basics:

    - "let" is the start of a query

    - "in" is the closing of a query

    - each transformation step sits in between those 2 lines

    - each step line is ended by a comma, except the last one

    - "Source" is always the first step (Source Data)

    - After "in" you have the last step referenced

  • Find common part number amongst various suppiers

    Quote

    I want to know what those matching common part number is for all of the suppliers.

    Please give an example of what this means using the data you supplied. I am not sure what you are asking and what you wish to be displayed. Smaller samples with a before and after scenario would seriously help to get your a bona fide solution.