Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Useless row cleaning and adding column for name against each row

Hi, wondering how to quickly clean data in csv or text where i have to remove unwanted column and at the same time need extra column to get name of a item against its subiteams. 

 

So my data is in below format:

LST CELL:;    
C1234     
C1234 2021-09-06 23:19:14   
O&M #808488944   
%%/*1881108461*/LST CELL:;%%  
RETCODE = 0  Operation succeeded.  
      
Display static parameters of cells  
Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
0A False Normal Normal FALSE
1A False Normal Normal FALSE
8A False Normal Normal FALSE
9A False Normal Normal FALSE
10A False Normal Normal FALSE
11A False Normal Normal FALSE
(Number of results = 12)   
      
To be continued...    
'---    END     
C1234 2021-09-06 23:19:14   
O&M #808488944   
%%/*1881108461*/LST CELL:;%%  
RETCODE = 0  Operation succeeded.  
      
Display static parameters of cells  
Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
12A False Normal Normal FALSE
13A False Normal Normal FALSE
19A False Normal Normal FALSE
20A False Normal Normal FALSE
200A False Normal Normal TRUE
201A False Normal Normal TRUE
202A False Normal Normal TRUE
(Number of results = 12)   
      
2 reports in total    
'---    END     
      
      
LST CELL:;    
C456     
C456 2021-09-06 23:19:14   
O&M #808459764   
%%/*1881575862*/LST CELL:;%%  
RETCODE = 0  Operation succeeded.  
      
Display static parameters of cells  
Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
0A False Normal Normal FALSE
1A False Normal Normal FALSE
2A False Normal Normal FALSE
3A False Normal Normal FALSE
4A False Normal Normal FALSE
5A False Normal Normal FALSE
6A False Normal Normal FALSE
7A False Normal Normal FALSE
8A False Normal Normal FALSE
9A False Normal Normal FALSE
10A False Normal Normal FALSE
11A False Normal Normal FALSE
(Number of results = 12)   
      
To be continued...    
'---    END     
C456 2021-09-06 23:19:14   
O&M #808459764   
%%/*1881575862*/LST CELL:;%%  
RETCODE = 0  Operation succeeded.  
      
Display static parameters of cells  
Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
12A False Normal Normal FALSE
13A False Normal Normal FALSE
14A False Normal Normal FALSE
15A False Normal Normal FALSE
16A False Normal Normal FALSE
17A False Normal Normal FALSE
18A False Normal Normal FALSE
19A False Normal Normal FALSE
20A False Normal Normal FALSE
200A False Normal Normal TRUE
201A False Normal Normal TRUE
202A False Normal Normal TRUE
(Number of results = 12)   
      
2 reports in total    
'---    END     

 

 

and my required format is 

 

SiteLocal Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag Coverage Level Type
C12340A False Normal Normal FALSE NULL
C12341A False Normal Normal FALSE NULL
C12348A False Normal Normal FALSE NULL
C12349A False Normal Normal FALSE NULL
C123410A False Normal Normal FALSE NULL
C123411A False Normal Normal FALSE NULL
C123412A False Normal Normal FALSE NULL
C123413A False Normal Normal FALSE NULL
C123419A False Normal Normal FALSE NULL
C123420A False Normal Normal FALSE NULL
C1234200A False Normal Normal TRUE COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C1234201A False Normal Normal TRUE COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C1234202A False Normal Normal TRUE COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C4560A False Normal Normal FALSE NULL
C4561A False Normal Normal FALSE NULL
C4562A False Normal Normal FALSE NULL
C4563A False Normal Normal FALSE NULL
C4564A False Normal Normal FALSE NULL
C4565A False Normal Normal FALSE NULL
C4566A False Normal Normal FALSE NULL
C4567A False Normal Normal FALSE NULL
C4568A False Normal Normal FALSE NULL
C4569A False Normal Normal FALSE NULL
C45610A False Normal Normal FALSE NULL
C45611A False Normal Normal FALSE NULL
C45612A False Normal Normal FALSE NULL
C45613A False Normal Normal FALSE NULL
C45614A False Normal Normal FALSE NULL
C45615A False Normal Normal FALSE NULL
C45616A False Normal Normal FALSE NULL
C45617A False Normal Normal FALSE NULL
C45618A False Normal Normal FALSE NULL
C45619A False Normal Normal FALSE NULL
C45620A False Normal Normal FALSE NULL
C456200A False Normal Normal TRUE COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C456201A False Normal Normal TRUE COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C456202A False Normal Normal TRUE

 COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off

 

So here I need one extra column and add Site Name C123 taken from one of the row and mentioned against its Local Cell ID

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Anonymous ,

     

    Sorry for the late reply.

    Check below M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZbdatswFMdf5ZAR2ErdSort2Bm76GIHCp4DrXvV9UJV1MxMsYxks/Vt8ix5sikKW7/cYjQKLQT7Qjrn/PRhn/+RLi8HU0xG/uBw8OC9OrxzbNYEEeyh2EPhrjua4HiC/Yfh8+8tQiT8ZkM+RCjyoyj2/SfDDofHBziKMDYRIT44zs4LmKZZNvk8HHYt4ywtpvMkhS+ANmuY11zRppQV6JYxzhd8cdRFddmSUteC3oJuzAgMaqroijdcaZA3wLgQuovKJKMCpsYNp4ndnG3nht319BLKalEy2khlLRe1KKufwG6Z2E6j+E35GwSvls0P60/kr+rliPyrdyqL3aQzQZd2Ici4Tqx7RoXeTZ5LtaLicXN2kp2nlsEOTOTAxC5rc9qQy44+5u3qmqvtb1Zct6LRJpsw+dQ3cQoJ1xyYrJqyak3CHXWmnM3/sed5m/X2gTRP9rJ6+7LCxCUNRy6Qi0qIi0oI6k8VZxd/of7augf1/3z/oP/VIzFYLZXBygoa2djxXfXY13YnqE5V+0H4gt1Z7EE8Dp8XezAOopDsxf7mzlCXmuJSUnwHJnBgQgdm7MDs7x6ve/d4r9Xo6g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"LST CELL:;" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LST CELL:;", type text}, {"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"LST CELL:;"],"C")=true then [#"LST CELL:;"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",7),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Local Cell ID", type text}, {" Cell Name", type text}, {" Csg indicator", type text}, {" Uplink cyclic prefix length", type text}, {" Downlink cyclic prefix length", type text}, {" NB-IoT Cell Flag", type text}, {"C1234", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type1",6),
    Custom1 = #"Changed Type1",
    #"Removed Top Rows1" = Table.Skip(Custom1,17),
    #"Kept First Rows1" = Table.FirstN(#"Removed Top Rows1",7),
    Custom2 = #"Changed Type1",
    #"Removed Top Rows2" = Table.Skip(Custom2,39),
    #"Kept First Rows2" = Table.FirstN(#"Removed Top Rows2",12),
    Custom3 = Table.Combine({#"Kept First Rows",#"Kept First Rows1",#"Kept First Rows2"})
in
    Custom3

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

5 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Anonymous ,

     

    Take the first table for example:

    Using below M codes to get the site name:

     

    = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Source, each Text.StartsWith([#"LST CELL:;"],"C"))[#"LST CELL:;"]{0})

     

    And you will see:

    Then remove the unwanted rows:

    For the whole M codes is as below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nU9Na4NAEP0rg8VLiM2uEVktPaR+QMAqNPaU5rCsGyvduOIa2vyb/Jb8str1UNJKocIcZua9N/PedmsE2F46xty4qt38G7icbWRjC3kWcodx6WPPx841PXs5ImS7j5pyQxBxCPEc59dZ01zMMCEY9wwXzxbJJocgShL/zjTHbDxFeZCFEdwDupwha3hLu0rWoI6McV7w4nZMNbYLK9UIegLV9RcYNLSlB97xVoHcA+NCqDFVIhkVEPQwrEMdTvdprx0mVUJVFxWjnWz15rkRVf0G7MTE15uW76sPELwuu1eNh/K9/puRPlhrmQ9PY0FLbQT10ErDMRVqeJ7K9kDFzzZeJZtIa/AEDZmg8aZ4mxTo34l2nw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"LST CELL:;" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LST CELL:;", type text}, {"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Source, each Text.StartsWith([#"LST CELL:;"],"C"))[#"LST CELL:;"]{0}),
    #"Removed Top Rows" = Table.Skip(#"Added Custom",7),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Local Cell ID", Int64.Type}, {" Cell Name", type text}, {" Csg indicator", type logical}, {" Uplink cyclic prefix length", type text}, {" Downlink cyclic prefix length", type text}, {" NB-IoT Cell Flag", type logical}, {"C1234", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"C1234", "Site"}, {" Cell Name", "Cell Name"}, {" Csg indicator", "Csg indicator"}, {" Uplink cyclic prefix length", "Uplink cyclic prefix length"}, {" Downlink cyclic prefix length", "Downlink cyclic prefix length"}, {" NB-IoT Cell Flag", "NB-IoT Cell Flag"}}),
    Custom1 = Table.ReorderColumns( #"Renamed Columns",{"Site","Local Cell ID","Cell Name","Csg indicator","Uplink cyclic prefix length","Downlink cyclic prefix length","NB-IoT Cell Flag"})
in
    Custom1

     

    And you will see:

    After changing all the tables in this way,append them to get the requested table.

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for the feedback. But I am still not able to get hold of it. The M code make an expandable table and gave error. 

    One more thing the "LST CELL:;" command will be repreated many times in my data with new site id. Will this code work for that as well. Will be much appreciated if you look at it. 

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi  Anonymous ,

       

      Does the sample data you provided exist in one table?Or from several tables?

       

      Best Regards,
      Kelly

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Sample data is actully in one table but there are gaps and extra rows with redundant data that is not required. Like below it is one sigle sheet. 

     

     

     

    LST CELL:;    
    C1234     
    C1234 2021-09-06 23:19:14   
    O&M #808488944   
    %%/*1881108461*/LST CELL:;%%  
    RETCODE = 0  Operation succeeded.  
          
    Display static parameters of cells  
    Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
    0A False Normal Normal FALSE
    1A False Normal Normal FALSE
    8A False Normal Normal FALSE
    9A False Normal Normal FALSE
    10A False Normal Normal FALSE
    11A False Normal Normal FALSE
    (Number of results = 12)   
          
    To be continued...    
    '---    END     
    C1234 2021-09-06 23:19:14   
    O&M #808488944   
    %%/*1881108461*/LST CELL:;%%  
    RETCODE = 0  Operation succeeded.  
          
    Display static parameters of cells  
    Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
    12A False Normal Normal FALSE
    13A False Normal Normal FALSE
    19A False Normal Normal FALSE
    20A False Normal Normal FALSE
    200A False Normal Normal TRUE
    201A False Normal Normal TRUE
    202A False Normal Normal TRUE
    (Number of results = 12)   
          
    2 reports in total    
    '---    END     
          
          
    LST CELL:;    
    C456     
    C456 2021-09-06 23:19:14   
    O&M #808459764   
    %%/*1881575862*/LST CELL:;%%  
    RETCODE = 0  Operation succeeded.  
          
    Display static parameters of cells  
    Local Cell ID Cell Name Csg indicator Uplink cyclic prefix length Downlink cyclic prefix length NB-IoT Cell Flag
    0A False Normal Normal FALSE
    1A False Normal Normal FALSE
    2A False Normal Normal FALSE
    3A False Normal Normal FALSE
    4A False Normal Normal FALSE
    5A False Normal Normal FALSE
    6A False Normal Normal FALSE
    7A False Normal Normal FALSE
    8A False Normal Normal FALSE
    9A False Normal Normal FALSE
    10A False Normal Normal FALSE
    11A False Normal Normal FALSE
    (Number of results = 12)   
          
    To be continued...    
    '---    END     
    C456 2021-09-06 23:19:14   
    O&M #808459764   
    %%/*1881575862*/LST CELL:;%%  
    RETCODE = 0  Operation succeeded.  

     

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi  Anonymous ,

       

      Sorry for the late reply.

      Check below M codes:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZbdatswFMdf5ZAR2ErdSort2Bm76GIHCp4DrXvV9UJV1MxMsYxks/Vt8ix5sikKW7/cYjQKLQT7Qjrn/PRhn/+RLi8HU0xG/uBw8OC9OrxzbNYEEeyh2EPhrjua4HiC/Yfh8+8tQiT8ZkM+RCjyoyj2/SfDDofHBziKMDYRIT44zs4LmKZZNvk8HHYt4ywtpvMkhS+ANmuY11zRppQV6JYxzhd8cdRFddmSUteC3oJuzAgMaqroijdcaZA3wLgQuovKJKMCpsYNp4ndnG3nht319BLKalEy2khlLRe1KKufwG6Z2E6j+E35GwSvls0P60/kr+rliPyrdyqL3aQzQZd2Ici4Tqx7RoXeTZ5LtaLicXN2kp2nlsEOTOTAxC5rc9qQy44+5u3qmqvtb1Zct6LRJpsw+dQ3cQoJ1xyYrJqyak3CHXWmnM3/sed5m/X2gTRP9rJ6+7LCxCUNRy6Qi0qIi0oI6k8VZxd/of7augf1/3z/oP/VIzFYLZXBygoa2djxXfXY13YnqE5V+0H4gt1Z7EE8Dp8XezAOopDsxf7mzlCXmuJSUnwHJnBgQgdm7MDs7x6ve/d4r9Xo6g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"LST CELL:;" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LST CELL:;", type text}, {"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"LST CELL:;"],"C")=true then [#"LST CELL:;"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",7),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Local Cell ID", type text}, {" Cell Name", type text}, {" Csg indicator", type text}, {" Uplink cyclic prefix length", type text}, {" Downlink cyclic prefix length", type text}, {" NB-IoT Cell Flag", type text}, {"C1234", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type1",6),
    Custom1 = #"Changed Type1",
    #"Removed Top Rows1" = Table.Skip(Custom1,17),
    #"Kept First Rows1" = Table.FirstN(#"Removed Top Rows1",7),
    Custom2 = #"Changed Type1",
    #"Removed Top Rows2" = Table.Skip(Custom2,39),
    #"Kept First Rows2" = Table.FirstN(#"Removed Top Rows2",12),
    Custom3 = Table.Combine({#"Kept First Rows",#"Kept First Rows1",#"Kept First Rows2"})
in
    Custom3

      And you will see:

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

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