Search:

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

Page 1 of 5 1 2 3 4

Search: Search took 0.01 seconds.

  1. Re: Connect fact sheets with different dimension

    If you're working in Power Pivot looks like you need to create a many to many relationship.
    The solution to this is to create two extra tables with lists of unique IDs and Qualifications linked to a...
  2. Re: Power Query: Unpivot + Unstack 2 tabluar Tables into List

    Sorry for the delay in replying I've been away for the weekend.
    To keep it simple don't worry about using a function yet.
    You could follow these steps.
    Create query linked to first data table and...
  3. Re: Power Query: Unpivot + Unstack 2 tabluar Tables into List

    The way I tackle these kinds of patterns is to merge the first three columns; delimit with a unique txt string. I often use##. You can then unpivot the date columns. Next split the merged column...
  4. Replies
    3
    Views
    655

    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,...
  5. 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
  6. 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
  7. 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...
  8. Replies
    1
    Views
    555

    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
  9. Replies
    11
    Views
    1,447

    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...
  10. 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...
  11. Replies
    20
    Views
    1,643

    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 =...
  12. Replies
    20
    Views
    1,643

    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...
  13. Replies
    20
    Views
    1,643

    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...
  14. Replies
    20
    Views
    1,643

    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
  15. Replies
    20
    Views
    1,643

    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...
  16. Re: Custom or Conditional Column to Copy data to Null Cells

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

    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
  18. 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
  19. Replies
    6
    Views
    1,280

    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...
  20. Replies
    6
    Views
    1,280

    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",...
  21. 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...
  22. 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
  23. Replies
    21
    Views
    1,316

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

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

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