Search:

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

Page 1 of 5 1 2 3 4

Search: Search took 0.01 seconds.

  1. Replies
    3
    Views
    198

    Re: Weighted Measure - Different Criterias

    Are you happy to consider using Power Query?
    The general approach would be to un-pivot the first table then merge with the second matching on Rank Group and Metric, multiply by the weighting,...
  2. Re: Power Query to Concatenate Rows Based on Condition

    Not sure what NVM means but I'm glad it worked and also you understood what the code was doing and helps you on your Power Query learning adventure!

    Peter
  3. Re: Power Query to Concatenate Rows Based on Condition

    Sorry Sandy666
    for crossing my post with yours. I hope I haven't confused the issue.

    Peter
  4. Re: Power Query to Concatenate Rows Based on Condition

    AS an alternative approach use Text.Combine:


    let
    Source = Excel.CurrentWorkbook(){[Name="InputTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type...
  5. Replies
    1
    Views
    289

    Re: lookupvalue same table

    Load the table to PQ.
    Duplicate it.
    In the copy filter by type ="e".
    Group by id return max of date.
    Merge this query back with original joined on id; expand on the max date.

    Peter
  6. Re: lookup initial entry in multiple transfers

    Dorin,
    Horseyride posted his code just before I was about to post something very similar using the same recursive function approach. I have run my version and his for a test file of 50000 records...
  7. Re: Error: We Cannot Convert a value of Type Function to Type list

    Shouldn't the line

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    Actually be:
    #"Converted to Table" = Table.FromList(#"Invoked...
  8. Replies
    20
    Views
    1,547

    Re: Power Query column alignment

    Ben,
    After a lot of messing about because I knew nothing about JSON files I came up with this:

    let
    Source =...
  9. Replies
    20
    Views
    1,547

    Re: Power Query column alignment

    Ben,
    Am I correct in thinking this is a JSON format file?
    Could I suggest you save the file to your PC then connect to PQ with the from web option. I haven't used this technique myself but for the...
  10. Replies
    20
    Views
    1,547

    Re: Power Query column alignment

    Ben,
    I've just rechecked the results and have found a few more problems with the data. It appears the problem is with the source. Although it looks like a CSV file the text in the fields also...
  11. Replies
    20
    Views
    1,547

    Re: Power Query column alignment

    Ben,
    I should have added that when you Pivot in the last step you need to click advanced and select "Don't aggregate" at the bottom of the drop down list.

    Peter
  12. Replies
    20
    Views
    1,547

    Re: Power Query column alignment

    This is messy but should work.
    1 Add index column
    2 Unpivot other columns, don't aggregate.
    3 Remove column with previous headers.
    4 Split column of data at first colon.
    5 Re-pivot

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

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

    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
  15. 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
  16. Replies
    6
    Views
    1,168

    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...
  17. Replies
    6
    Views
    1,168

    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",...
  18. 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...
  19. 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
  20. Replies
    21
    Views
    1,209

    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...
  21. Replies
    21
    Views
    1,209

    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...
  22. Replies
    21
    Views
    1,209

    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...
  23. 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...
  24. 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...
  25. 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.
Results 1 to 25 of 106
Page 1 of 5 1 2 3 4