cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sethigurpreer Frequent Visitor
Frequent Visitor

Transform Data Problem

Hi All,

 

Good Morning.

 

I am working on a parking dataset where I have a column which has values like:

 

2P DIS M-SUN 0:00-7:30

4P DIS ONLY M-SUN

P/15 M-SUN 0:00-23:59- No Park

P/15 M-SUN 0:00-23:59 - No Park

 

 

I want to break this information like

 

Col1   Col2          Col3    Col4              Col5

2P      DIS            M-SUN 0:00-7:30

4P      DIS ONLY              0:00-23:50

P/15                     M-SUN 0:00-23:59   No Park 

P/15                     M-SUN 0:00-23:59   No Park

 

I am working on this method where first gran location of all spaces in between the text and then try to read individual records. Problem is this is returing data in LIST format (when I click on this LIST it shows data in a different table). Question is how can I use this List output (like an array) in power query or any other approach to break this data.

 

Regards

Gurpreet Sethi

6 REPLIES 6
Super User
Super User

Re: Transform Data Problem

Not sure what your problem is:

Does the list contain broken pieces of your original table already (like shown below) or does it contain positions separators (list of number only)? Or is this one list only?

 

Please post picture or share your code that we can work from there.

 

PBI_SplitList.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Transform Data Problem

What is your datasource?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

sethigurpreer Frequent Visitor
Frequent Visitor

Re: Transform Data Problem

Hi MrJolly,

 

My data source is a csv file. This column stores Parking Signs data.

sethigurpreer Frequent Visitor
Frequent Visitor

Re: Transform Data Problem

Hi ImkeF,

 

Well my data is stored like this

 

Capture.JPG

 

and i want to transform it to something like this

 

Capture.JPG

 

Hope I am able to explain myself.

 

Regards

Gurpreet Sethi

 

Super User
Super User

Re: Transform Data Problem

Strange that it comes in one column. Have you played around with the delimters and encoding?

However, this would be the code to split according to your example:

et
    Quelle = Csv.Document(File.Contents("D:\BI\Foren\CSVTest.csv"),[Delimiter=",", Encoding=1252]),
    PromoteHeaderOptional = Table.PromoteHeaders(Quelle),
    MergeDisOnly = Table.ReplaceValue(PromoteHeaderOptional,"DIS ONLY","DIS_ONLY",Replacer.ReplaceText,{"Column1"}),
    RemoveNoPark = Table.ReplaceValue(MergeDisOnly,"- No Park","",Replacer.ReplaceText,{"Column1"}),
    SplitColumn = Table.SplitColumn(RemoveNoPark,"Column1",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    Column4 = Table.AddColumn(SplitColumn, "Column4", each if Text.Start([Column1.4],2)="0:" then [Column1.4] else if Text.Start([Column1.3],2)="0:" then [Column1.3] else ""),
    Column3 = Table.AddColumn(Column4, "Column3", each if Character.ToNumber(Text.Start([Column1.3],1))>58 then [Column1.3] else [Column1.2]),
    Column2 = Table.AddColumn(Column3, "Column2", each if Character.ToNumber(Text.Start([Column1.3],1))>58 then [Column1.2] else ""),
    CleanUp = Table.SelectColumns(Column2,{"Column1.1", "Column2", "Column3", "Column4"})
in
    CleanUp

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




austinsense Established Member
Established Member

Re: Transform Data Problem

Sorry I posted on the wrong issue and I can't delete the post - please ignore

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy