Search found 929 matches

by MattAllington
Wed Nov 29, 2017 7:39 am
Forum: PowerPivot/DAX
Topic: Get value for next date based on a condition
Replies: 3
Views: 629

Re: Get value for next date based on a condition

I guess you can do this in Power Query with a simple if statement. Power Query is case sensitive.

Add a custom step

=if [entry] >= #datetime(0,0,0,14,0,0) and [exit] >= #datetime(0,0,0,0,0,0) then "something" else "something else"
by MattAllington
Tue Nov 28, 2017 7:38 am
Forum: Power Query
Topic: Which tables/queries are using most space and refresh time
Replies: 3
Views: 708

Re: Which tables/queries are using most space and refresh time

Hi Ian Is it Power Pivot or Power BI? If Power Pivot, use https://www.sqlbi.com/tools/power-pivot-utilities/ to check the table size. If it is Power BI, you could try my local host workbook https://exceleratorbi.com.au/measure-dependencies-power-bi/ For Queries, you may need to write some VBA to rec...
by MattAllington
Tue Nov 28, 2017 7:34 am
Forum: PowerPivot/DAX
Topic: Updating data srouce with new columns
Replies: 1
Views: 547

Re: Updating data srouce with new columns

Excel 2016 has a bug. You should be able to go into Table Properties in Power Pivot, edit the query and add the 2 columns back with the wizard. In some version of Excel 2016 this option is greyed out and you can't make any changes. But you could still try this. Design\Table Properties
by MattAllington
Tue Nov 28, 2017 7:31 am
Forum: PowerPivot/DAX
Topic: Get value for next date based on a condition
Replies: 3
Views: 629

Re: Get value for next date based on a condition

I recorded this video that does something similar in Power Query. https://www.youtube.com/watch?v=xN2IRXQ2CvI

Could you use this to add a date and time stamp to the table before loading?
by MattAllington
Tue Nov 21, 2017 6:25 pm
Forum: Power Query
Topic: Using Parameters to select a column
Replies: 1
Views: 642

Re: Using Parameters to select a column

Hi Suzy

Do you have a sample you can post instead of the code? I haven't looked, but is there an end of month function in Power Query to find the last day of the current month eg EOM(Today()) [not real code] to work it out?
by MattAllington
Tue Nov 21, 2017 6:20 pm
Forum: PowerPivot/DAX
Topic: Average per month
Replies: 2
Views: 731

Re: Average per month

I've looked at this file and can't work out what you are trying to do. For me, the average per month is the running monthly total divided by the days. That is not what your excel sheet seems to do
by MattAllington
Tue Nov 21, 2017 6:06 pm
Forum: Power Query
Topic: Import from folder: use part of filename as value in addl. column
Replies: 2
Views: 749

Re: Import from folder: use part of filename as value in addl. column

Yes. What version of Excel do you have? Excel 2016 should do this almost automatically. Simply combine and edit. The auto process should add the source file name to the import. From there you can you extract the date information from the file name to use it in your table. Post back if that doesn't m...
by MattAllington
Mon Nov 20, 2017 6:30 pm
Forum: Power Query
Topic: Can I use ‘Enter Data’ to load old data once (incremental refresh)? [Solved]
Replies: 3
Views: 1183

Re: Can I use ‘Enter Data’ to load old data once (incremental refresh)? [Solved]

Agreed - votes are important. It seems you have found the SOLVED feature :-)
by MattAllington
Mon Nov 20, 2017 7:05 am
Forum: PowerPivot/DAX
Topic: Calculated Column If Column Has A Value
Replies: 1
Views: 517

Re: Calculated Column If Column Has A Value

I am sure this can be done, but I am more interested as to why you want to do this. Power Pivot is very different to Excel, and maybe there is a better way to do what you want - I can't be sure because I don't know what you are trying to achieve.
by MattAllington
Mon Nov 20, 2017 7:00 am
Forum: Power Query
Topic: Can I use ‘Enter Data’ to load old data once (incremental refresh)? [Solved]
Replies: 3
Views: 1183

Re: Can I use ‘Enter Data’ to load old data once (incremental refresh)? [Solved]

This feature is currently in planning https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7288623-incremental-data-loads There is an option to turn off the query refresh for individual queries (include in report refresh). You could load a history file into Power BI and then turn off t...

Go to advanced search