Table.Profile applied to each Table .Group (GroupKind,.Local & SQL set ordered): too much RAM

[Anonymous]

Hi,

Here is my pseudo M query code:

Source = select * from ms sql-server table ordered by indexed id 

#GroupedRows = Table.Group(Source, 3 columns (one of which is the indexed id & the other 2 columns values repeat as a group), ... , GroupKind.Local)

dt = Add Table.Profile to each table as a column to #GroupedRows

#RemovedColumns = Remove the table column  so keep the 3 groups & table.profile only

#ExpandProfile = Expand the data profile

in

#ExpandProfile

It works fine with limited data set but not with the full data set. The full data set is 9.5mn rows & 650 columns. There are about ~25 odd groups using the 3 columns but the number of groups are not known in advance so i need a dynamic solution. I have turned data previews off. I see 4 mashup container in task manager & they go to nearly 99% of  32gb RAM at which point the power bi pretty much stops pulling in any rows & gets stuck.

Is it possible to iterate through the list of tables, run table.profile, save it, drop the underlying data & repeat? I'm not sure how to accomplish that with M query, if helpful at all. Any other ideas? I'm not sure how to manage the memory issue.

Thanks for your help.

[Anonymous]

i've made the following change but no luck yet: instead of adding a column table type of table.profile, i do table.transformcolumn of the data & apply table.profile instead. i've tried switching off parallel load also but load gets stuck at 96% RAM eventually & CPU usage of container averaging around 3% only..

[lbendlin]

What is the actual question you are trying to answer? Some sort of a correlation analysis?

650 columns will be slow in any scenario. Can you please explain what your grouping criteria are, or maybe give an example?

[Anonymous]

@lbendlin i should also say that initially i had split up all the groupings manually as separate queries & the profile took about 1.5hrs. i turned off parallel & background previews. not sure if that helps. thanks. i am trying to make it dynamic though as the groupings will change frequently. 

[lbendlin]

Frankly I wouldn't bother with Power Query for that.  There are tools like Alteryx or Knime that are more specialized and (in the case of Alteryx) have far better performance.

Anyway - would you be able to  provide a sample extract that can demonstrate the issue?

[Anonymous]

@lbendlin thanks for your message. i am trying to just get the summary of the data (min, max, etc) using the native function table.profile. i customize table.profile with BlankCount but nothing more. 

i am trying to profile a large flat file. it is grouped by company name, typeofdata (revenue, etc) & numericid of the report. i have indexed the id & i sort it by the id in Source. the number of companies are dynamic. i could remove the company name & typeofreport as a test but i don't think the grouping is the holdup. i need csv's basically.

here is the m query now:

let
Source = Sql.Database("servername", "database", [Query="select * from data order by id asc"]),

#"Grouped Rows" = Table.Group(Source, {"companyname", "type", "id"}, {{"dt", each _, type table [..,...,]}}, GroupKind.Local),

profile = Table.TransformColumns(#"Grouped Rows", {"dt", each Table.Profile(_, {
{
"BlankCount", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = ""))
}
}
)}),
expandedprofile = Table.ExpandTableColumn(profile, "dt", {"Column", "Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount", "BlankCount"}, {"Column", "Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount", "BlankCount"})
in
expandedprofile