Forum Discussion
KelvinUceta
1 year agoRegular Visitor
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...
- Anonymous1 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
1 year agoSuper User
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.
- KelvinUceta1 year agoRegular Visitor
Clear, thank you very much