Forum Discussion
Convert a list into multiple column table
- 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.
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.
- KelvinUceta1 year agoRegular Visitor
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.