Forum Discussion
Split table in multiple tables automitcally based on category
- Anonymous7 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.
Hi Anonymous ,
You can use Group By and then add each table as a new query.
You can refer to this video: Power Query - Split Master Table into Sub Tables by unique Column Keys.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous7 years agoNot applicable
Hi Icey ! Thank you a lot ! Your suggestion is really good and is nearly what I want. It works fine but it is not really automatic. I would like to not do the same operations for each procedure.
I'm looking for a way to do so but if you have any suggestion it would be great !