Search:

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

Page 1 of 3 1 2 3

Search: Search took 0.01 seconds.

  1. Re: abnormal power pivot error message 'string cannot be converted to date type'

    I just had this and fixed it. I had an existing power query feeding a data model. I had replaced the power query with another one (cutting and pasting via the Advanced editor). When I did, one of...
  2. Re: abnormal power pivot error message 'string cannot be converted to date type'

    FYI, I'm getting this as well. (I had an existing PowerPivot with a PowerQuery as source....the error popped up with I made some changes to the power query. No joy in figuring out how to fix...
  3. Re: How to modify Power Pivot data connections in old workbooks? How to convert to Power Query?

    I only just noticed that there are Home->Clipboard->Paste Append/Paste Replace buttons. That seems to work to update the data in those connections. Weird that I'm only seeing/noticing them now. ...
  4. How to modify Power Pivot data connections in old workbooks? How to convert to Power Query?

    Hello,

    I only started using PowerPivot after Power Query was readily available. I come across example Excel/PowerPivot documents where the data is embedded into the Data Model and I don't...
  5. Replies
    1
    Views
    114

    Translate Fill Color to a Value

    Hello All,
    Is there a way to transform a cell's fill color into some sort of data?

    (I've inherited a bunch of worksheets where each cell has a number value and then the fill color says what the...
  6. Re: Count # of customers that bought both apples and pears

    Hi,
    I looked at the basket analysis (Thanks!) but I'm not quite getting it....or more to the point....I couldn't figure out how to do that plus integrate it with the rest of the thing I'm working...
  7. Replies
    3
    Views
    6,320

    Re: How to Hide Columns in Power Query editor

    you can't hide it from the view in PowerQuery......(you can remove it though...second row down on your screen shot)
    if you want to hide it from the end user in the Excel client, then once you have...
  8. Re: Count # of customers that bought both apples and pears

    Hi citizenbh,
    No....
    if you add Bob | Apple
    as another line, then you still get a 1 with your formula....it's counting the two Apple lines, getting 2 (and therefore setting to 1)
    (and sorta the...
  9. Re: Count # of customers that bought both apples and pears

    Hi Comfy, using that, it does the comparrison for the fruit on the same row. (which, is always false). Breaking it down some more, I guess I need to get a list of customers that are equal to...
  10. Count # of customers that bought both apples and pears

    Hello!
    These seems deceptively simple but I can't find any example online that answer this (or am searching for the wrong thing)
    Any help would be great.

    Considering this table:

    <tbody>...
  11. How to return the student with the highest grade for their class

    Hi,
    Starting with this:
    https://i.imgur.com/O4NPsTL.png

    I'd like to end up with this in PowerBI
    https://i.imgur.com/7Urln5e.png


    The calculated column for TopStudent should be something...
  12. Re: How to return the rows that equal the current row (for a calculated column)

    Cheers for the PowerQuery line, that's great. I'll add that to my example notebook.
    In fact, I did as you mentioned and added some sorting info in my date(aka, grade) table.

    Thanks Owen!

    PS,...
  13. Re: How to return the rows that equal the current row (for a calculated column)

    Thanks Owen,
    That gives me the column i was looking for.......:)
    unfortunately, :( when i try to use it as the "sort by column", power bi complains about a circular dependency. "A circular...
  14. How to return the rows that equal the current row (for a calculated column)

    Hello,
    My end goal is to create a sortby column as shown here. (so that I can sort [school] by ... [sortby])
    I need the formula for the "sortby" calculated column.
    For named "Years"
    It should...
  15. Re: What's the best way of pulling data from the data model of another Excel workbook via PowerQuery?

    Interesting...

    Can you recommend a getting started with DAX Studio tutorial? (I've read about it but couldn't find anything on youtube....and last time I tried using it wasn't intuitive enough for...
  16. What's the best way of pulling data from the data model of another Excel workbook via PowerQuery?

    The set up is that I have an excel workbook where I do "stuff" in the Data Model.

    I'd like to use that stuff as an input for another Excel workbook.(is this a common setup?)
    Up until now I've...
  17. Re: Power Query: How to point to a file in the current directory?

    Thanks for the pointer, that's a good article.

    (and just noticed i messed up the markup in my message .....it should have said,


    C:\Users\ < user idsid > <idsid style="color: rgb(51, 51, 51);...
  18. Power Query: How to point to a file in the current directory?

    I'd like to be able to open a file in the current directory of the .XLS file that I'm running the Power Query refresh from. Is that possible?

    (the use case is, I have a have an export that I want...
  19. Re: Index to return a sum of corresponding matching rows

    just noticed the SUMIF formula's above have switched the RANGE and Sum_range fields ....... it's supposed to be SUMIF(range, criteria, [sum_range])....

    SO, for that last example....it's:
    ...
  20. Re: Index to return a sum of corresponding matching rows

    :eeek: This is my embarrassed face.


    Thanks Special-K.

    (As an aside, in the end I can just use A1 instead of the D1 since I want % of Apples/Bananas. IE, =B1/Sumif($B1:$B3,A1,$A1:$A3). I...
  21. Index to return a sum of corresponding matching rows

    <tbody>
    Fruit
    Number
    Sum



    Apple
    10
    =16
  22. Replies
    13
    Views
    1,498

    Re: countif([columnA],[@columnB]) in DAX or M?

    Awesome stuff. I appreciate you all building off of each other's replies as it lets me work through them all :)
    In the end I ended up with the last reply from Bill. It's interesting how you...
  23. Replies
    13
    Views
    1,498

    Re: countif([columnA],[@columnB]) in DAX or M?

    For M, this works ( although it could probably be cleaned up...the important part is in bold). I got the hint from miguel's article.
    I hope the conversion between list and table can be done...
  24. Replies
    13
    Views
    1,498

    Re: countif([columnA],[@columnB]) in DAX or M?

    Hi Imke,
    I'm afraid that this also yields the same results where the comparison is just between the two columns on the same row.
    Thanks for the suggestion though,
    Alex
  25. Replies
    13
    Views
    1,498

    Re: countif([columnA],[@columnB]) in DAX or M?

    AH!
    I was looking for this:
    =COUNTROWS(FILTER(theTable, EARLIER([columnA])=[columnB]))

    Where 1 is returned if the current row columnA is found anywhere in columnB and (blank) is returned if it's...
Results 1 to 25 of 51
Page 1 of 3 1 2 3