Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a text file which is formated like this:
Date and Time
measure1 measure2 measure3 measure4 measure5 measure6
title1 wholenum wholenum wholenum wholenum wholenum text
title2 wholenum wholenum null null null null
<<blank line>>
measure7 measure8 measure9 measure10
title3 wholenum wholenum wholenum wholenum
title4 wholenum wholenum wholenum null
<<blank line>>
measure11 measure12 measure13 measure14 measure15 measure16
title5 text text wholenum wholenum wholenum wholenum
title6 text text wholenum wholenum wholenum wholenum
Date and Time
measure1 measure2 measure3 measure4 measure5 measure6
title1 wholenum wholenum wholenum wholenum wholenum text
title2 wholenum wholenum null null null null
<<blank line>>
measure7 measure8 measure9 measure10
title3 wholenum wholenum wholenum wholenum
title4 wholenum wholenum wholenum null
<<blank line>>
measure11 measure12 measure13 measure14 measure15 measure16
title5 text text wholenum wholenum wholenum wholenum
title6 text text wholenum wholenum wholenum wholenum
This is a source system generated file being supplied to me. I am tryin to work with the analytics but transforming it into a useable file is dificult.
Can someone assit me in transforming that source file into a format like this?
Date and Time
Meas1 Meas2 Meas3 Meas4 Meas5 Meas6 Meas7 Meas8 Meas9 Meas10 Meas11 Meas12 Meas13 Meas14 (etc)
title1
title2
title3
title4
title5
title6
The assumption here is that all measure are to be column headings, and any values that do not exist for a given title would be set to NULL.
Thanks
Solved! Go to Solution.
That shouldn't be a problem. My query is designed to operate on a singe table. So we transfer it to a function that you can use in a new column where it then can operate on every row of a table that holds one table in another column.
You don't have to define this in a separate query as you've done with your "Transform File from DB - Read Write Monitor Files"-function, but use code like this instead:
let // Query transferred to function, that takes a table as input parameter fnTransformTable = (table) => let Source = table, #"Filtered Rows" = Table.SelectRows(Source, each ([col1] <> "")), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "TableNumber", each Number.IntegerDivide([Index],3)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"TableNumber"}, {{"Table", each Table.PromoteHeaders(Table.RemoveColumns(_,{"TableNumber"})), type table}}), Cleanup = Table.RemoveColumns(#"Grouped Rows",{"TableNumber"}), AllColumnNames = List.Union(List.Transform(Cleanup[Table], each Table.ColumnNames(_))), #"Expanded Table" = Table.ExpandTableColumn(Cleanup, "Table", AllColumnNames), #"Renamed Columns" = Table.RenameColumns(#"Expanded Table",{{"", List.First(Table.ColumnNames(Source))}}) in #"Renamed Columns", // Your code so far Source = Folder.Files("H:\docs\DB - SVmon Files"), #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from DB - Read Write Monitor Files", each #"Transform File from DB - Read Write Monitor Files"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from DB - SVmon Files"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from DB - SVMon Files", Table.ColumnNames(#"Removed Other Columns1"[#"Transform File from DB - SVmon Files"]{0})), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}), // create a new column where the function that transforms our table is called #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each fnTransformTable([Column1])), Cleanup = Table.SelectColumns(#"Added Custom1",{"Custom"}), AllColumnNames = List.Union(List.Transform(Cleanup[Custom], each Table.ColumnNames(_))), #"Expanded Custom" = Table.ExpandTableColumn(Cleanup, "Custom", AllColumnNames) in #"Expanded Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Ouch. Invoking @ImkeF
Check out this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRyk1NLC4tSjVEMI2UYnWilUoyS3LAooYGQMLIACFoBOSbgASNIYJAFghBmVBDjBFME4RWY7B5EAORTDQBmwgxEo+ZpgimGalmxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"06.04.2017" = _t, col1 = _t, col2 = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([col1] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "TableNumber", each Number.IntegerDivide([Index],3)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"TableNumber"}, {{"Table", each Table.PromoteHeaders(Table.RemoveColumns(_,{"TableNumber"})), type table}}),
Cleanup = Table.RemoveColumns(#"Grouped Rows",{"TableNumber"}),
AllColumnNames = List.Union(List.Transform(Cleanup[Table], each Table.ColumnNames(_))),
#"Expanded Table" = Table.ExpandTableColumn(Cleanup, "Table", AllColumnNames),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Table",{{"", List.First(Table.ColumnNames(Source))}})
in
#"Renamed Columns"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I understand your code but 2 issues I have.
First being the source is a folder containing multiple text files labeled svmon<<date>>.log
Second when I try to work the source in to your code, I paste it into the advance editor and I get no results, There are no syntax errors but nothing happens to the data? How do I get the Power BI application to apply the code against the data in the Edit Query mode??
Here is my code (red being my source info, black being yours:
let
Source = Folder.Files("H:\docs\DB - SVmon Files"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from DB - Read Write Monitor Files", each #"Transform File from DB - Read Write Monitor Files"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from DB - SVmon Files"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from DB - SVMon Files", Table.ColumnNames(#"Removed Other Columns1"[#"Transform File from DB - SVmon Files"]{0})),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Filtered Rows" = Table.SelectRows(Source, each ([col1] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "TableNumber", each Number.IntegerDivide([Index],3)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"TableNumber"}, {{"Table", each Table.PromoteHeaders(Table.RemoveColumns(_,{"TableNumber"})), type table}}),
Cleanup = Table.RemoveColumns(#"Grouped Rows",{"TableNumber"}),
AllColumnNames = List.Union(List.Transform(Cleanup[Table], each Table.ColumnNames(_))),
#"Expanded Table" = Table.ExpandTableColumn(Cleanup, "Table", AllColumnNames),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Table",{{"", List.First(Table.ColumnNames(Source))}})
in
#"Changed Type"
That shouldn't be a problem. My query is designed to operate on a singe table. So we transfer it to a function that you can use in a new column where it then can operate on every row of a table that holds one table in another column.
You don't have to define this in a separate query as you've done with your "Transform File from DB - Read Write Monitor Files"-function, but use code like this instead:
let // Query transferred to function, that takes a table as input parameter fnTransformTable = (table) => let Source = table, #"Filtered Rows" = Table.SelectRows(Source, each ([col1] <> "")), #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "TableNumber", each Number.IntegerDivide([Index],3)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"TableNumber"}, {{"Table", each Table.PromoteHeaders(Table.RemoveColumns(_,{"TableNumber"})), type table}}), Cleanup = Table.RemoveColumns(#"Grouped Rows",{"TableNumber"}), AllColumnNames = List.Union(List.Transform(Cleanup[Table], each Table.ColumnNames(_))), #"Expanded Table" = Table.ExpandTableColumn(Cleanup, "Table", AllColumnNames), #"Renamed Columns" = Table.RenameColumns(#"Expanded Table",{{"", List.First(Table.ColumnNames(Source))}}) in #"Renamed Columns", // Your code so far Source = Folder.Files("H:\docs\DB - SVmon Files"), #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from DB - Read Write Monitor Files", each #"Transform File from DB - Read Write Monitor Files"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from DB - SVmon Files"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from DB - SVMon Files", Table.ColumnNames(#"Removed Other Columns1"[#"Transform File from DB - SVmon Files"]{0})), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}), // create a new column where the function that transforms our table is called #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each fnTransformTable([Column1])), Cleanup = Table.SelectColumns(#"Added Custom1",{"Custom"}), AllColumnNames = List.Union(List.Transform(Cleanup[Custom], each Table.ColumnNames(_))), #"Expanded Custom" = Table.ExpandTableColumn(Cleanup, "Custom", AllColumnNames) in #"Expanded Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |