Forum Discussion

sethigurpreer's avatar
sethigurpreer
Frequent Visitor
10 years ago

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

Need Help

6 Replies

Replies have been turned off for this discussion
  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    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's avatar
        sethigurpreer
        Frequent Visitor
        10 years ago

        Hi MrJolly,

         

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

    • sethigurpreer's avatar
      sethigurpreer
      Frequent Visitor
      10 years ago

      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

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        10 years ago

        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