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 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.
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
- Greg_Deckler
Community Champion
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
- ImkeF
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.
v-juanli-msftCommunity Support
Hi Anonymous
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 RegardsMaggie
