Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.02 seconds.

  1. Re: Changing Connection via VBA (Loop through queries)

    Well, youíve got me there. Seems the .Queries collection, properties etc were added in Excel 2016 so I am 3 years (or 1 in the case of my office) out of date.

    I apologise for my behaviour.
  2. Replies
    8
    Views
    1,018

    Re: Struggling with ALL function and filters

    Well youíve got me there. I didnít see you
    Mentioned a second table. Would be interested to see your final measure.
  3. Replies
    8
    Views
    1,018

    Re: Struggling with ALL function and filters

    Yeah, you really donít. IMO my formulation is better. Put the Parts Req where I had the COUNTROWS line.
  4. Re: Changing Connection via VBA (Loop through queries)

    https://stackoverflow.com/questions/36902975/auto-updating-power-query-connection-via-vba

    Here is an example.
  5. Re: Changing Connection via VBA (Loop through queries)

    Thanks for not bothering to check before saying I was wrong. Power Query queries are Connections in VBA.
  6. Re: Changing Connection via VBA (Loop through queries)

    You probably want “ThisWorkbook.Connections”
  7. Replies
    8
    Views
    1,018

    Re: Struggling with ALL function and filters

    I still can’t understand why you are using that summarize formulation, but glad you cracked it.
  8. Replies
    8
    Views
    1,018

    Re: Struggling with ALL function and filters

    Are you wanting to have “Line Items” filtered for the date range? It’s not in your formulas above.

    If I understand correctly your formula should be more like this:


    CALCULATE(
    AVERAGEX(...
  9. Replies
    4
    Views
    1,040

    Re: DAX formula for accruals

    What do you get if you replace the MIN in your last measure with MAX, and then plot on a daily timeline? How is it different from what you want to see?
  10. Replies
    4
    Views
    1,040

    Re: DAX formula for accruals

    All the below IMHO:

    Accruals are a balance sheet item and are normally a semi additive measure. In this case you are building them up though so a little different. Even so, you want to calculate...
  11. Replies
    2
    Views
    1,011

    Re: Power Query: >90 Queries?

    That is quite a painful API...

    This isn't too hard, definitely don't make 90 queries. My steps would probably be:

    1. Really check that I can't push this to an API call, some APIs allow you to...
  12. Thread: TopN Required

    by gazpage
    Replies
    7
    Views
    831

    Re: TopN Required

    Well, no. Second clause of the topn just needs to be the table you want the top2 rows of.
  13. Thread: TopN Required

    by gazpage
    Replies
    7
    Views
    831

    Re: TopN Required

    Yeah, but your measure isnít finding the top 2 spenders, itís finding the top 2 measures on the measuretable. Look at the second parameter of your TOPN
  14. Thread: TopN Required

    by gazpage
    Replies
    7
    Views
    831

    Re: TopN Required

    What is MeasuresTable? Why would you want the two highest rows from that table?
  15. Replies
    5
    Views
    1,034

    Re: Get transformed table from 2 input tables PQ

    If you want to DM me I can send you my file. It's a PBIX, but at least you can copy the actual queries and see how it works. The errors are the same thing, where it isn't producing an RRUL column,...
  16. Replies
    3
    Views
    734

    Re: Comparing for allowable items

    The easy solution is to do an anti join in Power Query.

    Get the two tables ready, chose Merge Queries as New, select the two table and the two columns in each to merge on (Ctrl Click), Join Kind...
  17. Replies
    5
    Views
    1,034

    Re: Get transformed table from 2 input tables PQ

    Sorry, I ran out of time. Wherever it says // that is a new query that you need to paste into advanced editor on its own. The part directly after the // is the name you need to call the query. I...
  18. Replies
    4
    Views
    724

    Re: M Code - change field names

    = Table.RenameColumns(#"Pre Change",Table.ToRows(Changes))
  19. Replies
    5
    Views
    1,034

    Re: Get transformed table from 2 input tables PQ

    // Source
    let
    Source =Web.Page(Web.Contents("https://www.mrexcel.com/forum/power-bi/1091265-get-transformed-table-2-input-tables-pq.html")),
    Data0 =Source{0}[Data],
    #"ChangedType" =...
  20. Re: DAX Measure to rank by category and subcategory

    Just google RANKX DAX. You will need another parameter, I forget if is is a 0 or 1.
  21. Re: DAX Measure to rank by category and subcategory

    Given you havenít had a response...

    You want something like:

    =RANKX(
    ALLEXCEPT( Product, Category ),
    SUM( Reference )
    )
  22. Replies
    3
    Views
    580

    Re: How does this formula Work in Power BI

    Itís just creating a date from year, quarter and month.

    The year and the day of 1, are presumably self explanatory. The month calc just uses the month and quarter, so for May quarter is 2 and...
  23. Replies
    1
    Views
    390

    Re: Accrual Calculation

    Iíve literally just done a report on trade debtors and trade creditors based on a list of invoices.

    My solution was like.

    [Debtors]:=

    VAR EndDate = MAX ( Calendar[Date] )

    RETURN
  24. Replies
    1
    Views
    415

    Re: Distinct Count problem

    I don't think I understand your question, otherwise this would just be something like:


    Measure:=
    VAR TotalSoldUnits = SUM ( 'Sales Table'[Units Sold])

    RETURN
    SWITCH (
    TRUE(),
    ...
  25. Re: Struggling to understand circular reference

    I personally haven't done that, but otherwise you might need to use ALLEXCEPT in your measure, which is pretty tedious.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4