Transform Data Problem

[sethigurpreer]

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

[ImkeF]

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.

[sethigurpreer]

Hi ImkeF,

Well my data is stored like this

and i want to transform it to something like this

Hope I am able to explain myself.

Regards

Gurpreet Sethi

[austinsense]

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

[ImkeF]

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

[Phil_Seamark]

What is your datasource?

[sethigurpreer]

Hi MrJolly,

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