Search:

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

Page 1 of 2 1 2

Search: Search took 0.00 seconds.

  1. Replies
    1
    Views
    671

    Re: Top 5 Customer in Power BI

    Rank = RANKX(ALL(Sales[Customer name]),
    SUM(Sales[Total_Sales]),DESC)
  2. Replies
    3
    Views
    346

    Re: Dynamic Array Constant

    Ha! Evaluate in a named range...that's perfect! I did not know that EVALUATE could be used outisde VBA. Irobbos suggestion was also considered but I failed to mention that my client has acute...
  3. Replies
    3
    Views
    346

    Dynamic Array Constant

    Is it possible to reference another cell to use as an array constant?

    Let's say in table A I have the following data:


    <tbody>

    A
    B
  4. Replies
    2
    Views
    248

    VBA Workbook Attributes/Properties

    Hello all,

    So, I'm trying to gather several properties for a number of workbooks in a folder:



    Original Author
    Creation Date
    File Size
    File Type
  5. Re: [Formula Assistance] Date to weekday and sum

    =SUMPRODUCT(--(WEEKDAY(Sheet1!A:A)=2))

    Where 1 = Sunday, 2 = Monday...and so on.
  6. Replies
    14
    Views
    618

    Re: Trying to avoid helper column formula

    Hi Bill,

    I always tell people that they can do alot with a simple sumif and vlookup. Sumproduct does both...try this:



    =SUMPRODUCT(
    (--(DataSheet!B:B=A3))
    +(--(DataSheet!C:C=A3))...
  7. Replies
    2
    Views
    157

    Re: Folder Picker

    That did the trick. Thanks Joe!
  8. Replies
    2
    Views
    157

    Folder Picker

    I'm trying to loop through files in a folder and I'd like to allow the user to select the folder that they would like to loop through. What am I doing wrong here:




    Sub GetSummaryData()
    ...
  9. Replies
    5
    Views
    681

    Re: Extracting duplicate data

    I think you may have answered your own question:
  10. Re: Visual needs to show months by year month not Jan thru Dec

    It sounds like you need a custom calendar to accommodate your fiscal period. Try this:

    https://powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/
  11. Re: Data Model telling me there are duplicates I can't find

    I had a similar problem a while back. Have you tried cleansing for whitespace? Go to your excel table and wrap the column in =Clean(Trim(TextField)). I would then apply conditional formatting to...
  12. Re: Power Query - does it iterate the data set for every command?

    Hi macfuller,

    Thought this might help:

    https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/
  13. Re: PowerPivot - Import data and append new data on a daily basis

    Power Query doesn't store or overwrite data it just brings the data into your model. It's the source file that's being overwritten. Email a copy of that CSV file to your IT department and let them...
  14. Re: PowerPivot - Import data and append new data on a daily basis

    Where is this CSV report coming from? It sounds like somebody put together a script to place that report in that folder on a daily basis. If your PQ model is updating with updated data my guess is...
  15. Re: Power Pivot Slicers; Hide Data Items with No Data (Against Specific Measures)

    Country Revenue Last 12...
  16. Replies
    5
    Views
    853

    Re: Measure can't decide if it's a date??

    Don't use the MAX measure on dates...use LASTDATE:

    MAX ( 'Calendar'[Date] )

    becomes:

    LASTDATE('Calendar'[Date])

    And FYI the MIN equivalency is FIRSTDATE.
  17. Replies
    6
    Views
    1,761

    Re: Forecasting in Power Query or Powerpivot?

    Hi Macfuller,

    I'm glad you enjoyed the model, I found it entertaining.

    While building the model I did make note that I left the "Cycle (x)" field in a Date format but disregarded because I did...
  18. Replies
    6
    Views
    1,761

    Re: Forecasting in Power Query or Powerpivot?

    Convert the dataset you posted to a table and name it RegressionDataSet.

    Create a blank query and name it RegressionSourceData; then copy and paste this code into the query editor:



    // Query...
  19. Replies
    14
    Views
    585

    Re: Extract names before and after a /

    Click on the Data tab above your ribbon

    Click on the text to columns button in the Data Tools Section

    Select Delimited

    Enter "v" in the others option

    Click ok
  20. Replies
    4
    Views
    867

    Re: Count formula in POwer Pivot

    Try using two formulas:

    TOTAL LOAN COUNT:=COUNTROWS(LoanList)

    WOMEN OWNED LOAN COUNT:=CALCULATE([TOTAL LOAN COUNT],FILTER(LoanList,LoanList[WOMEN OWNED]="100"))
  21. Replies
    6
    Views
    1,761

    Re: Forecasting in Power Query or Powerpivot?

    Interesting...I recently refined my depreciation technique and have been wanting to apply it to forecasting. You're going to need several things. First, you'll have to take your data out of a...
  22. Re: Is it possible to create named constants in Power Query

    Kind of spit balling here but have you tried integrating a custom list into your parameterized logic?

    ={[Option=0,Desc="Order.Ascending"],[Option=1,Desc="Order.Descending"]}
  23. Replies
    3
    Views
    667

    Re: Lookup nth item in a column

    Which position are you looking for? List position in the sales table or the discount table?
  24. Re: Looking to create weekly OH for 52 weeks. How do i calculate the dates

    Can't you just add a week count to your calendar table?
  25. Re: Power Query to Extract Multiple Tabs from Most Recent Daily Excel Report

    Thank you both for both the excellent suggestions. In the end, I built the first query and just copied the code and pasted into a blank query and augmented as needed for product type and tab name. ...
Results 1 to 25 of 31
Page 1 of 2 1 2