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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GarryFarrell
Advocate III
Advocate III

Folder Data Source and Pivot

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

1 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 🙂

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

View solution in original post

4 REPLIES 4
ImkeF
Super User
Super User

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)

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

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}}),

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 🙂

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

thank you I will try your solution now.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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