Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Split table in multiple tables automitcally based on category

Hello everyone,  I have one big table like this :    file_id procedure_ref label value 10001 procedure_1 Date 20 septembre 2019 10001 procedure_1 Voiture VW 10001 procedure...
  • Anonymous's avatar
    Anonymous
    6 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
        Custom1

    FnDetectType 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.