Forum Discussion

GorujamBI's avatar
GorujamBI
Regular Visitor
2 years ago
Solved

Transformation data

I got data from Web using API, i want to know how to transform the lines in columns and more important! merge the keys...

Like "Name":"Valour" ... i have many Names ... the Names have to be in the Coluns ans Valour following below

 

Invoice, NF-e, Start, Approval comercial, Approove comercial, Approval financeiro, Approove financeira, Brought, Price, Seller, UF, Tipe, Register... these names have to become Coluns and values following bellow

 

 

[
{
"Invoice": "072046",
"NF-e": null,
"Start": "2024-08-22",
"Approval comercial": "FRANCISCO",
"Approove comercial": "2024-08-22 09:37:59",
"Approval financeiro": "ALINE",
"Approove financeira": "2024-08-22 10:25:08",
"Brought": null,
"Price": 1137.34,
"Seller": "39 IGOR MARTINS CARVALHO",
"UF": "SP",
"Tipe": "12",
"Register": "2020-04-09"
},
{
"Invoice": "072047",
"NF-e": null,
"Start": "2024-08-22",
"Approval comercial": "FRANCISCO",
"Approval financeiro": "2024-08-22 10:12:14",
"Approval financeiro": "ALINE",
"Approove financeira": "2024-08-22 10:26:23",
"Brought": null,
"Price": 4664.69,
"Seller": "39 IGOR MARTINS CARVALHO",
"UF": "RS",
"Tipe": "12",
"Register": "2022-04-04"
}

]

 

Tips and Tricks
  • NaveenGandhi's avatar
    NaveenGandhi
    2 years ago

    Hi GorujamBI 

    Below m code will work. Just replace the source step according to your source.

    let
    Source = Json.Document(
    "[{
            ""Invoice"": ""072046"",
            ""NF-e"": null,
            ""Start"": ""2024-08-22"",
            ""Approval_comercial"": ""FRANCISCO"",
            ""Approove_comercial"": ""2024-08-22 09:37:59"",
            ""Approval_financeiro"": ""ALINE"",
            ""Approove_financeira"": ""2024-08-22 10:25:08"",
            ""Brought"": null,
            ""Price"": 1137.34,
            ""Seller"": ""39 IGOR MARTINS CARVALHO"",
            ""UF"": ""SP"",
            ""Tipe"": ""12"",
            ""Register"": ""2020-04-09""
        },
        {
            ""Invoice"": ""072047"",
            ""NF-e"": null,
            ""Start"": ""2024-08-22"",
            ""Approval_comercial"": ""FRANCISCO"",
            ""Approove_comercial"": null,
            ""Approval_financeiro"": ""ALINE"",
            ""Approove_financeira"": ""2024-08-22 10:26:23"",
            ""Brought"": null,
            ""Price"": 4664.69,
            ""Seller"": ""39 IGOR MARTINS CARVALHO"",
            ""UF"": ""RS"",
            ""Tipe"": ""12"",
            ""Register"": ""2022-04-04""
        }]"
    ),

    TableData = Table.FromList(Source, Record.FieldValues, {"Invoice", "NF-e", "Start", "Approval_comercial", "Approove_comercial", "Approval_financeiro", "Approove_financeira", "Brought", "Price", "Seller", "UF", "Tipe", "Register"})
in
    TableData

     Let me know if this helps.

    If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

    Follow me on LinkedIn!!!

1 Reply

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    2 years ago

    Hi GorujamBI 

    Below m code will work. Just replace the source step according to your source.

    let
    Source = Json.Document(
    "[{
            ""Invoice"": ""072046"",
            ""NF-e"": null,
            ""Start"": ""2024-08-22"",
            ""Approval_comercial"": ""FRANCISCO"",
            ""Approove_comercial"": ""2024-08-22 09:37:59"",
            ""Approval_financeiro"": ""ALINE"",
            ""Approove_financeira"": ""2024-08-22 10:25:08"",
            ""Brought"": null,
            ""Price"": 1137.34,
            ""Seller"": ""39 IGOR MARTINS CARVALHO"",
            ""UF"": ""SP"",
            ""Tipe"": ""12"",
            ""Register"": ""2020-04-09""
        },
        {
            ""Invoice"": ""072047"",
            ""NF-e"": null,
            ""Start"": ""2024-08-22"",
            ""Approval_comercial"": ""FRANCISCO"",
            ""Approove_comercial"": null,
            ""Approval_financeiro"": ""ALINE"",
            ""Approove_financeira"": ""2024-08-22 10:26:23"",
            ""Brought"": null,
            ""Price"": 4664.69,
            ""Seller"": ""39 IGOR MARTINS CARVALHO"",
            ""UF"": ""RS"",
            ""Tipe"": ""12"",
            ""Register"": ""2022-04-04""
        }]"
    ),

    TableData = Table.FromList(Source, Record.FieldValues, {"Invoice", "NF-e", "Start", "Approval_comercial", "Approove_comercial", "Approval_financeiro", "Approove_financeira", "Brought", "Price", "Seller", "UF", "Tipe", "Register"})
in
    TableData

     Let me know if this helps.

    If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

    Follow me on LinkedIn!!!