Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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.

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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