Apending Rows

[darrylbeckett]

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

[Greg_Deckler]

Ouch. Invoking @ImkeF

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

[darrylbeckett]

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"

[ImkeF · Accepted 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"