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
roncruiser
Helper V
Helper V

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
before.jpg

Would like to add the column number which identifes the data set.

after.jpg

 

1 ACCEPTED SOLUTION

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.

 

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

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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 Regards
Maggie

It 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

Anonymous
Not applicable

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 PQMessy data import into PQ

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

Messy Data after transformation in PQMessy Data after transformation in PQ

Best

D

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

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

This is a way better solution than mine!  Thanks!

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.

Top Solution Authors
Top Kudoed Authors