Transform dataset

[boa]

Hello

I have problems to transform a dataset. I tried unpivot, ... but it gives not the result that I want.

Can I do this in PowerQuery?

Greetings

[boa]

@AntrikshSharm

Thank you for your help!

[AntrikshSharma · Accepted solution]

@boa Paste this code in advanced editor:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "hc7BDcAgDAPAXfJGqmNQgFkQ+69RXrSlDc0vOtlJaxIxRgmFShCYHqYs0sMvMbmUeBEPG8snran8JJhL5CSrbuFKt8JSlw9nocIt3FGGd+tFm1SE9H4C",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ #"ID accident" = _t, #"Code vehicle" = _t ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "ID accident", Int64.Type }, { "Code vehicle", type text } }
        ),
    Group = 
        Table.Group (
            ChangedType,
            { "ID accident" },
            {
                {
                    "Count",
                    each
                        let
                            VehicleCodes = _[Code vehicle],
                            CodeCount = List.Count ( VehicleCodes ),
                            ColumnNames = List.Transform (
                                { 1 .. CodeCount },
                                each "Code.vehicle." & Text.From ( _ )
                            ),
                            CodeAndColumnNames = { ColumnNames } & { VehicleCodes },
                            ToTable = Table.PromoteHeaders ( Table.FromRows ( CodeAndColumnNames ) )
                        in
                            ToTable
                }
            }
        ),
    ExpandedCount = 
        Table.ExpandTableColumn (
            Group,
            "Count",
            { "Code.vehicle.1", "Code.vehicle.2", "Code.vehicle.3" },
            { "Code.vehicle.1", "Code.vehicle.2", "Code.vehicle.3" }
        ),
    ChangedType2 = 
        Table.TransformColumnTypes (
            ExpandedCount,
            {
                { "Code.vehicle.1", type text },
                { "Code.vehicle.2", type text },
                { "Code.vehicle.3", type text }
            }
        )
in
    ChangedType2