Forum Discussion
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 identifier.
So far I've been unsuccessful or uncreative getting this to work.
Any help would be immensely helpful...
Thank you...
Current Format
Would like to add the column number which identifes the data set.
Hi roncruiser
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.
6 Replies
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
Yikes. ImkeF might be able to help. Posting a small amount of text data would likely greatly assist here in recreating the issue and testing a solution. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi roncruiser
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.
This is a way better solution than mine! Thanks!
Hi roncruiser
Is this problem sloved?If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?If not, please feel free to let me know.Best RegardsMaggieIt was solved but I solved it on my own with sheer desperation.
Though the solutions provided on this thread were much better and are very similar to what I concocted.
Some of which I will adopt myself.
Thank you community.
Ron
