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
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
Solved! Go to Solution.
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.
@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
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 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
Your query is a solid start point for me, really appreciate.
Greets
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |