Folder Data Source and Pivot

[GarryFarrell]

Hi,

I have used the folder data source. In the latest version of PBI Desktop it automates most of the steps. My code is below. I have an issue where I end up with duplicate rows and the pivot fails for the duplicate rows. I would like to add the line number of each file to see if this helps with the duplicate issue. I have tried adding a index column just before the pivot but that give me a martix like solution and the data is on multiple rows. The index is from 1 to the end which is the end of all files. I want to add the row number of each file and the row number will reset to 1 when the next file is processed.

let
    Source = Folder.Files("C:\Users\Data"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform Binary from Data", each #"Transform Binary from Data"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform Binary from Data"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform Binary from Data", Table.ColumnNames(#"Removed Other Columns1"[#"Transform Binary from Data"]{0})),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Column1], " | +- 00000000") then "DataRaw" else if Text.Contains([Column1], "UTC") then "DateRaw" else if Text.StartsWith([Column1], " Header:") then "MachineRaw" else null ),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Source.Name", "Custom", "Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Category"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Category]), "Category", "Column1"),
    DataRaw = #"Pivoted Column"{0}[DataRaw]
in
    DataRaw

[ImkeF]

So you need a nested index. See how this is done here: https://community.powerbi.com/t5/Desktop/Adding-conditional-unique-index-column-based-on-several/td-...

You just need the 2n step "Grouped Rows" and then expand. (The TelIndex and HasMatches are some specific steps for the other solution there)

[GarryFarrell]

Hi Imke,

Thanks for your help so far. However would you please help me with the Table.Group command?

Based on my code that I posted above, is my code here correct? Also which line should I put it on?

    #"Grouped Rows" = Table.Group(Source, {"Source.Name"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}),

[ImkeF · Accepted solution]

Hi Garry,

yes, looking good in general. You bind it in like this (and add 2 additional steps): 

... your query up til here so far ...  
  #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Category", type text}}),
  //  #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
Group = Table.Group(#"Changed Type1", {"Source.Name"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}),
Remove = Table.RemoveColumns(#"Grouped Rows",{"Source.Name"})
Expand = Table.ExpandTableColumn(Remove, "All", Table.ColumnNames(Remove[All]{0}), Table.ColumnNames(Remove[All]{0}))
    #"Pivoted Column" = Table.Pivot(Expand, List.Distinct(Expand[Category]), "Category", "Column1"),
    DataRaw = #"Pivoted Column"{0}[DataRaw]
in
    DataRaw

syntax might not be 100% correct, so pls come back with error-message if it fails 🙂

[GarryFarrell]

thank you I will try your solution now.