Forum Discussion
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 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
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.
6 Replies
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"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
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.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.
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.
Clear, thank you very much
