Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
Hi @roncruiser
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
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):
And here's the output of the M code above:
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.
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!