Search:

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

Page 1 of 4 1 2 3 4

Search: Search took 0.01 seconds.

  1. Replies
    18
    Views
    661

    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 =...
  2. Replies
    18
    Views
    661

    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...
  3. Replies
    18
    Views
    661

    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...
  4. Replies
    18
    Views
    661

    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
  5. Replies
    18
    Views
    661

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

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

    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
  8. 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
  9. Replies
    6
    Views
    1,116

    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...
  10. Replies
    6
    Views
    1,116

    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",...
  11. 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...
  12. 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
  13. Replies
    21
    Views
    1,032

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

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

    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...
  16. 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...
  17. 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...
  18. 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.
  19. 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...
  20. Replies
    5
    Views
    923

    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...
  21. 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...
  22. 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
  23. 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....
  24. 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...
  25. Replies
    5
    Views
    805

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