Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KelvinUceta
Regular 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 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:

KelvinUceta_0-1726592074703.png

 

Any help is welcome

 

Thx

1 ACCEPTED SOLUTION
v-fenling-msft
Community Support
Community Support

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:

vfenlingmsft_0-1726821531418.png

 

vfenlingmsft_1-1726821643983.png

 

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:

vfenlingmsft_2-1726821688113.png

 

 

 

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.

 

View solution in original post

6 REPLIES 6
parry2k
Super User
Super 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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Clear, thank you very much

v-fenling-msft
Community Support
Community Support

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:

vfenlingmsft_0-1726821531418.png

 

vfenlingmsft_1-1726821643983.png

 

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:

vfenlingmsft_2-1726821688113.png

 

 

 

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.

 

parry2k
Super User
Super User

@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"


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

KelvinUceta_1-1726633065625.png

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

 

Greets

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.