Forum Discussion
Split table in multiple tables automitcally based on category
- Anonymous6 years ago
Here is my m code :
(ProcedureName as text) => let Source = PostgreSQL.Database("ServerName", "DBName"), public_form_field_large_dataset = Source{[Schema="public",Item="form_field_large_dataset"]}[Data], #"Grouped Rows" = Table.Group(public_form_field_large_dataset, {"procedure_name"}, {{"Tables", each _, type table [procedure_name=text, label=text, value=text, dossier_id=text]}}), demarche_1 = #"Grouped Rows"{[procedure_name=ProcedureName]}[Tables], #"Pivoted Column" = Table.Pivot(demarche_1, List.Distinct(demarche_1[label]), "label", "value"), Custom1 = let FnDetectType = (#"Pivoted Column", ColumnName)=> let ListColumn = Table.Column(#"Pivoted Column",ColumnName), NumberPercentage = List.Count( List.Select(ListColumn , each Value.Is(Value.FromText( _ ), type number)) ) / List.Count(ListColumn), IntPercentage = List.Count( List.Select(ListColumn , each Value.Is(Value.FromText( _ ), type number)) ) / List.Count(ListColumn), DatePercentage = List.Count( List.Select(ListColumn , each Value.Is(Value.FromText( _ ), type date)) ) / List.Count(ListColumn), DateTimePercentage = List.Count( List.Select(ListColumn , each Value.Is(Value.FromText( _ ), type datetime)) ) / List.Count(ListColumn), Max = List.Max( {NumberPercentage, DatePercentage, DateTimePercentage, IntPercentage} ), Result = if Max < .95 then {ColumnName, type text} else if DateTimePercentage = Max then {ColumnName, type datetime} else if DatePercentage = Max then {ColumnName, type date} else if IntPercentage = Max then {ColumnName, Int64.Type} else {ColumnName, type number} in Result, ConvertTypesList = List.Transform( Table.ColumnNames(#"Pivoted Column"), each FnDetectType(#"Pivoted Column", _) ), ConvertedTable = Table.TransformColumnTypes(#"Pivoted Column", ConvertTypesList) in ConvertedTable in Custom1FnDetectType is a function to detect the data type of a column.
With this solution I have to manually enter the name of the procedure that I want to analyse (only the first time or at each change of structure). It's acceptable and will requiere less manipulation for the client.
If the client wants to do it himself he will have to (for a new procedure or if there is a change in the structure of the procedure) :
- open the pbix
- go to the Query Editor
- clic on the function
- enter the name of the procedure that he wants to analyse
- rename the new query with the name of the procedure
- clic on "apply and close"- do some graphs in the visual view
- publish the report
I don't think I could be simplier. If you have any idea don't hesitate.
I don't think there is a method in M that does that, especially when values are dynamic.
But...why? I see no possible use of such a manipulation. Everything can be filtered (and dynamically) in PowerBI, so why?
- Anonymous6 years agoNot applicable
Hi Anonymous , thanks for your response.
I dont explain fully my problem. I have one big table (the first one) and I have to let people that know less in Power BI do their own analysis and visuals with Power BI.
To do so, my first solution was to pivot the table on the label value a create a table with a lot of columns and because one label is unique for a procedure, there is a lot of empty columns and I think that in one or two years (maybe more) my system will become too slow.Another solution is that each procedure is one table to resolve this problem of empty cells. Moreover, in a ideal case, I wouldnt have to create myself a new table when the client create a new procedure.
And my question is how to achieve that ?
- Anonymous6 years agoNot applicable
Well, generally speaking empty columns are NOT an issue with memory in powerbi due to the way data is compressed in memory. You can have 200 columns for 1.000.000 rows with emtpy values and your memory footprint will be close to zero.
- Anonymous6 years agoNot applicable
Anonymous after few testing with 70 columns and 1 000 rows in only one table, it's very slow. I don't think it's a sustainable solution.
Icey I'm still looking to automatically generate new tables. For the moment a create a function where we must specify the name of the procedure.. it's an alternative but not totally the good solution.