Search:

Type: Posts; User: peter789; Keyword(s):

Page 1 of 4 1 2 3 4

Search: Search took 0.01 seconds.

  1. Re: Custom or Conditional Column to Copy data to Null Cells

    Did it fill you up?;)
  2. Replies
    2
    Views
    360

    Re: Power Query - Pivot with multiple values

    UnPivot the data first. Then merge Period column and the column with Amt1 and Amt2 in it. Pivot again.

    Peter
  3. Re: Looking for good Power Query Websites and Resources

    Sandy666,
    That last link you posted looks incredibly informative; thank you so much. Plenty of bed time reading, foresee can I.

    Peter
  4. Re: Add custom column that finds last reading

    I used the technique described here:

    https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/

    Note that Table.Buffer has to be placed around the Table.Sort function to...
  5. Re: Add custom column that finds last reading

    Does this work for you?

    let
    Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Meter Reading",...
  6. Re: Power Query Get Data From SharePoint Folder Pre-2016?

    Andy,
    Does it work if you just paste the code you're using in O365 into the older query in advanced editor.
    Possibly something like:


    Source = Excel.Workbook(Web.Contents("http://fullpath to...
  7. Re: Power Query slows down after merging queries or tables

    When developing the code for your query it is always possible to filter the data in the source queries first to a smaller subset. When it's all working fine delete the filters.

    Peter
  8. Replies
    21
    Views
    972

    Re: Park Run Data - Scotland

    I'm blocked out again. Looks like I have to wait a few hours, I've tried re-booting without success. I have tried finding the web site's Terms of service, it obviously doesn't like what we are...
  9. Replies
    21
    Views
    972

    Re: Park Run Data - Scotland

    I'll try again later and see if I can work out a way of limiting my calls to their server.
    I didn't realise you just wanted the latest. That makes it a bit easier. I will still try calling up the...
  10. Replies
    21
    Views
    972

    Re: Park Run Data - Scotland

    Unfortunately I get 403 forbidden on the website. However I had a look earlier and would think about the following approach. First create a table of the events you are interested in. Create a...
  11. Re: Calculate Units Picked 17:00 - 04:00am (Shift)

    Darren,
    Unfortunately although I can see your screen shots they make little sense as I cannot see what your input data looks like so I'm guessing what you are trying to achieve.
    Why subtract 17...
  12. Re: Calculate Units Picked 17:00 - 04:00am (Shift)

    I don't really understand your full requirements but how about adding a helper column in the Data Model to subtract 17 hours from the actual date time? Then create a column just containing the shift...
  13. Re: Writing Power Query (M language) Table.X functions that will dynamically tailor the table scope to each row of data

    OK
    Glad you found a solution elsewhere.
  14. Re: Writing Power Query (M language) Table.X functions that will dynamically tailor the table scope to each row of data

    It's not pretty but this is one way of doing it.
    First filter out ignore. Then replace null with zero if that is what you mean to do, otherwise filter out null.
    Then reference this query.
    Then...
  15. Replies
    5
    Views
    831

    Re: Noob question - Excel queries

    I don't really understand your data. However to avoid adding the helper column in the Excel table how about duplicating your query in PQ then grouping by your conditions columns and returning the...
  16. Re: Manually Entered Data Shifting After Refresh

    I'm surprised it doesn't work: speaking from experience with using this technique on a number of applications.
    You need to add the index on the first query after you merge or combine the data from...
  17. Re: Manually Entered Data Shifting After Refresh

    Glad it worked. By the way it is better to use the add index feature in PQ rather than manually create and maintain the column of numbers back in Excel.

    Peter
  18. Re: Manually Entered Data Shifting After Refresh

    Yes it's a brilliant technique to learn. Do a web search for self referencing queries and the answer should be there. Basically you add a unique reference (index) to each row in you original query....
  19. Re: Using Concatenate in DAX in PowerPivot as a Measure

    CONCATENATE only works with two text strings. You need to nest a second CONCATENATE function in the expression =CONCATENATE(Data[Height],CONCATENATE(" x ",Data[Width])). Or just use the & operator...
  20. Replies
    5
    Views
    670

    Re: DAX LOOKUPVALUE with IF/OR included?

    I can't help you with the DAX problem but I am sure it is soluble. I might be wrong be wrong but my way of working with the Power BI concept is to use Power Query (Get and Transform) for ETL...
  21. Replies
    5
    Views
    670

    Re: DAX LOOKUPVALUE with IF/OR included?

    I would be tempted to use Power Query. Load the Lookup Table and the Data table as Queries. Then merge the two, joining on the cost centres (use Ctrl to select the 2nd, 3rd and 4th columns). Expand...
  22. Re: G&T - Get Data from Range without converting it to a Table?

    Another sneaky way on a simple spread sheet is to set your range to the print area (this works well if you are importing from multiple identical spread sheets e.g. if each year/region etc is on...
  23. Re: Using Power Query Connections in Pivot Tables

    Why can't you load the results from the queries into the Data Model? It will easily handle the volume. Then use Power Pivot Tables?
  24. Re: Pulling data from the power query into a spreadsheet not working correctly??!!

    Tony,
    I don't think I completely understand how your data is organised and exactly how your dashboard is meant to look but I will make a couple of suggestions anyway. My first step would be to...
  25. Re: Help Power Query! Marge date fields to create datetime field

    Posted in error
Results 1 to 25 of 94
Page 1 of 4 1 2 3 4