Forum Discussion

KelvinUceta's avatar
KelvinUceta
Regular Visitor
1 year ago
Solved

Convert a list into multiple column table

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 mul...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

parry2k's avatar
parry2k
Super User
1 year ago

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's avatar
    KelvinUceta
    Regular Visitor
    1 year ago

    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