Convert a list into multiple column table

[KelvinUceta]

Hi,

I'm using AdGuardHome at home as DNS, odd enough there's no way to export the log data as a table but as list, that's why I'm looking for the way to convert a list (single column table) into multiple columns table.

Exported data looks like following where the first 4 rows are the future table columns names.

Time

Request
Response
Client
07:05:24
9/16/2024
proxy.myCia.com
Type: AAAA, Plain DNS
Processed
0.06 ms
10.26.32.43
MyNodeID
07:05:24
9/16/2024
proxy.myCia.com
Type: A, Plain DNS
Processed
0.06 ms
10.26.32.43
MyNodeID

Result should be somethging like this:

Any help is welcome

Thx

[parry2k]

@KelvinUceta the first line shown as json is just a manual data entry row, you can copy the line from the source onwards and copy it in your actual table. 

[KelvinUceta]

Clear, thank you very much

[v-fenling-msft · Accepted solution]

Thank for parry2k's concern about this issue.

Hi, @KelvinUceta 

I am glad to help you.

Maybe you can refer to my M code.
Open Power BI Desktop-->select transform data-->right click New blank query in Queries panel-->select Advanced Editor while copying the following M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnMTVWK1YlWCkotLE0tLoGyiwvy84ohEs45mal5EHFzKwNTKyMTMNtS39BM38gAyisoyq+o1MutdM5M1EvOzwWLhVQWpFopOAKBjkJATmJmnoKLXzBYJqAoPzm1uDg1Bcwz0DMwU8gtBrMNDfSMzPSMjfRMjMF830q//JRUTxfyraeG3bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    tolist = Table.ToList(Source),
    columnnames = List.FirstN(tolist,4),
    values = List.Range(tolist,4),
    IndexedList = List.Zip({values, List.Positions(values)}),
    AssignToSublist = (list, sublistCount) =>
        List.Transform(
            {0..sublistCount-1},
            each List.Select(list, (x) => Number.Mod(x{1}, sublistCount * 2) >= _ * 2 and Number.Mod(x{1}, sublistCount * 2) < (_ + 1) * 2)
        ),
    Result = List.Transform(AssignToSublist(IndexedList, 4), each List.Transform(_, each _{0}))
in
    Table.FromColumns(Result,columnnames)

This is the result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KelvinUceta]

Hi,

Great but I'm sorry, I'm confused, how? I saw it also in the @parry2k solution your source is a Json.Document? So first I need to convert the data to .json?

Of now I need to run this on real data... Can you please explain to me?

I don't get it.

[parry2k]

@KelvinUceta the output you showed doesn't correspond to your sample data, anyhow here is the M code, start new query, click advanced editor and paste the code. Tweak the data to test it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnMTVWK1YlWCkotLE0tLoGyiwvy84ohEs45mal5EHFzKwNTKyMTMNtS39BM38gAyisoyq+o1MutdM5M1EvOzwWLhVQWpFopOAKBjkJATmJmnoKLXzBYJqAoPzm1uDg1Bcwz0DMwU8gtBrMNDfSMzPSMjfRMjMF830q//JRUTxfyraeG3bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 4, 1, Int64.Type),
    #"Added Group" = Table.AddColumn(#"Added Index", "Group", each if  Number.Mod([Index],4) = 0 then [Index] else null, Int64.Type 
),
    #"Filled Down" = Table.FillDown(#"Added Group",{"Group"}),
    #"Added Column Number" = Table.AddColumn(#"Filled Down", "Column Number", each [Index]-[Group], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Column Number",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Column Number", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Column Number", type text}}, "en-CA")[#"Column Number"]), "Column Number", "Column"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Group"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type text}, {"Request", type text}, {"Response", type text}, {"Client", type text}})
in
    #"Changed Type1"

[KelvinUceta]

Hi @parry2k 

Thank you very much for this, actually the data do correspond, see following pic, biggest issue I have is that there're 2 lines for each occurance, so for instance if we take "Time" is not only the time but also the date

Your query is a solid start point for me, really appreciate.

Greets