Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding Number Column based on the Row Value for huge Data Sets

I have a single CSV containing 5000 sets of data.  Each set of data is identified by the value in the first row at the start of each data set. I'd like to create a column identifier from the row set...
Need Help
Show and Tell
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous 

    please check out this post: https://community.powerbi.com/t5/Desktop/Grouping-multiple-rows/m-p/969739#M464286 

     

    First you add an index column to your table and then another column that checks if the text starts with "loop". If so, then return the index-column if not, return null. Then fill down that new column and you have your groups. If you need a sequential numbering without gaps, check the 2nd sample in that blogpost how to proceed.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This looks to be rather easy with PQ and M... Here's the code:

let
	PathToFile = "C:\Your Folder\Messy Data.csv",
    Source = Csv.Document(File.Contents(PathToFile),
    	[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", 
    	each if Text.StartsWith([Column1], "loop no:") then Text.Select([Column1], {"0".."9"}) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "Loop No"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Loop No", "Column1", "Column2", "Column3"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each not Text.StartsWith([Column1], "loop no:"))
in
    #"Filtered Rows"

The file I imported looked like this (through the usual CSV connector which did the split on comma for me immediately):

Messy data import into PQ

And here's the output of the M code above:

Messy Data after transformation in PQ

Best

D