Hi Aye,

Please take anther look into the attached file.

The function does not need to perform so many steps, use a data query that takes source data and transforms it, then just refer to that in the other queries, just like in the attached file.

You will have to prepare a sample source file you are using, with fake data, so I can test with your data structure and your queries.

Hi Catalin,

I am now completed the step for function - FilteredID. Thank you so much. Phew!

I now have to take so much time putting an additional column which I never been encountered.Is that because my Custom column for invoking FilteredID is a table?How can I over come that "taking so much time" step.

Thank you.

Regards,

Aye

You can use Table.Buffer(previousStepName) before adding the new column, this will load the data table to memory instead of querying again and again the data table.

Hi Catalin,

I am not so sure what you mean.

I have the data table name "Data" and I cleaned them to get about 16000 lines only.

Then I used the blank query to do a function - FilteredIDI then duplicate the data table, deleting all the steps, source = Data and invoke the FilteredID function.After that whenever I add the column or filtering from that column, it takes me more than 30 mins for any move. It is impossible to work.

Can you please elaborate more about Table.Buffer(previousStepName) and how can I make data table to memory??

I am sorry, please let me know all the steps.

Thank you.

Regards,

Aye

instead of

source = Data

use:

Source=Table.Buffer(Data)

Then you will add your custom column.

Thanks, Catalin.

it is still extremely slow. 

Please see my screen shot. 

Under Other Queries[3], Data is my original table which I cleaned to about 16000 lines, which is not much.

fx FilteredID is a blank query which I have it with (ID)=> step

MedicareCSV is the duplication of Data table. Then I changed Source = Table.Buffer(Data).

Once I get the Table under FilteredID, I did not expend and I tried to add column for List.Count(List.Distinct([FilteredID][Category]).

This step takes me about 15 mins. then I tried to filtered out all "0" and it took me about 30 mins.

Where did I do wrong?

Thank you.

Regards,

Aye

Hi Aye,

No idea what you're doing there. Why is filteredID a blank query?

Also, what is "List.Count(List.Distinct([FilteredID][Category])" for? I used the function like this in the sample file I sent:

= Table.AddColumn(#"Changed Type", "Custom", each FilterID([ID]))

Why is MedicareCSV a duplicate of Data query? What's the use of this?

It was the FilterID function that needed that Buffer operation, please take time to look at the sample file, it should be very easy to replicate it into your real file.

Hi Catalin,

I have attached word document copying your recommendation and what I tried to replicate with your instructions.

Please have a look and advise me of anything I did incorretly which took me so long.

Thank you.

Regards,

Aye

Try to optimize the query, that's all you can do.

For example, you have this in FilterID function and in MedicareCSV:

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Facility", Int64.Type}, {"Days", Int64.Type}, {"Rate", type number}, {"Total", type number}}),

It's best to set the type in the Data query, then you can remove this step from FilterID and Medicare.

Use Source=Table.Buffer(Data) in MedicareCSV too.

In some cases, PQ is slow, there is not much you can do. Depends on how frequently you will refresh the query: 15 minutes per month is acceptable, but if you refresh it daily, it's not.

The alternative is Power Pivot, it should be faster.

Hi Catalin,

I will have to do that almost every day. It is still taking too much time and I will stop working with power query.

Shall we start all over again that in power pivot?

Please see attached and can you please provide me with a DAX formula for power pivot.

Thank you.

Regards,

Aye

Hi Aye,

Please try to create the DAX formula yourself. If/when you get stuck then you can share your attempt with us. Our job isn't to do your work for you, it's to help you implement the techniques taught in the course. If you need help with something not covered in the course then you can post your question in the public Power Pivot forum which is open to all users.

Mynda

Hi Mynda,

I have been trying to work on that from the first week of March and I am stuck with slow power query. 

Now that Catalin told me to check with power pivot. I studied power pivot about 2 months ago and I literally cant remember now.

Can you please refer me specific power pivot chapter for DAX formula which could solve my issue.

Thank you.

Regards,

Aye

Hi Aye,

I requested a file with your real queries since message no. 16: "You will have to prepare a sample source file you are using, with fake data, so I can test with your data structure and your queries."

Instead of sending pictures of your query, if you can send the file containing the queries will be much easier (for you).

You can do something else:

Load the data query to sheet, then in the FiterID function and Medicare CSV, instead of referring to :

Source=Table.Buffer(Data), refer to the processed data table from worksheet:
Source =Table.Buffer( Excel.CurrentWorkbook(){[Name="Data"]}[Content]),

Thanks, Catalin.

I cannot upload full length fake data file on the forum. I have the error message saying that the file is more than allowed.

Once I run those codes for function and MedicareCSV, it is extremely slow on full length file.

Your last coding reduces the time substantially but not at an ideal one.

The small data file is working fine with your codes.

I now think I could upload those on excel and try to fine solutions out of excel. Power query has done a lot of cleaning on my file which is more useful than Excel application.

The following formula is a very good one and it will be able to filter out most of my data. Can you please let me know any excel equivalent formula as follows:

  #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Show 0 if all categries are the same for the same ID", each if List.Count(List.Distinct([Custom][Category])) = 1 then 0 else "more than 1 category for the same id"),

Thank you once again.

Regards,

Aye

Hi Aye,

I'm sorry but we have to impose some restriction on the size of files uploaded to the site.  We don't have unlimited storage space.

You can use OneDrive or Dropbox to store files and then link to them from here.

Phil