Useless row cleaning and adding column for name against each row

[Anonymous]

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
0	A	False	Normal	Normal	FALSE
1	A	False	Normal	Normal	FALSE
8	A	False	Normal	Normal	FALSE
9	A	False	Normal	Normal	FALSE
10	A	False	Normal	Normal	FALSE
11	A	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
12	A	False	Normal	Normal	FALSE
13	A	False	Normal	Normal	FALSE
19	A	False	Normal	Normal	FALSE
20	A	False	Normal	Normal	FALSE
200	A	False	Normal	Normal	TRUE
201	A	False	Normal	Normal	TRUE
202	A	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
0	A	False	Normal	Normal	FALSE
1	A	False	Normal	Normal	FALSE
2	A	False	Normal	Normal	FALSE
3	A	False	Normal	Normal	FALSE
4	A	False	Normal	Normal	FALSE
5	A	False	Normal	Normal	FALSE
6	A	False	Normal	Normal	FALSE
7	A	False	Normal	Normal	FALSE
8	A	False	Normal	Normal	FALSE
9	A	False	Normal	Normal	FALSE
10	A	False	Normal	Normal	FALSE
11	A	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
12	A	False	Normal	Normal	FALSE
13	A	False	Normal	Normal	FALSE
14	A	False	Normal	Normal	FALSE
15	A	False	Normal	Normal	FALSE
16	A	False	Normal	Normal	FALSE
17	A	False	Normal	Normal	FALSE
18	A	False	Normal	Normal	FALSE
19	A	False	Normal	Normal	FALSE
20	A	False	Normal	Normal	FALSE
200	A	False	Normal	Normal	TRUE
201	A	False	Normal	Normal	TRUE
202	A	False	Normal	Normal	TRUE
(Number of results = 12)
2 reports in total
'---    END

and my required format is 

Site	Local Cell ID	Cell Name	Csg indicator	Uplink cyclic prefix length	Downlink cyclic prefix length	NB-IoT Cell Flag	Coverage Level Type
C1234	0	A	False	Normal	Normal	FALSE	NULL
C1234	1	A	False	Normal	Normal	FALSE	NULL
C1234	8	A	False	Normal	Normal	FALSE	NULL
C1234	9	A	False	Normal	Normal	FALSE	NULL
C1234	10	A	False	Normal	Normal	FALSE	NULL
C1234	11	A	False	Normal	Normal	FALSE	NULL
C1234	12	A	False	Normal	Normal	FALSE	NULL
C1234	13	A	False	Normal	Normal	FALSE	NULL
C1234	19	A	False	Normal	Normal	FALSE	NULL
C1234	20	A	False	Normal	Normal	FALSE	NULL
C1234	200	A	False	Normal	Normal	TRUE	COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C1234	201	A	False	Normal	Normal	TRUE	COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C1234	202	A	False	Normal	Normal	TRUE	COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C456	0	A	False	Normal	Normal	FALSE	NULL
C456	1	A	False	Normal	Normal	FALSE	NULL
C456	2	A	False	Normal	Normal	FALSE	NULL
C456	3	A	False	Normal	Normal	FALSE	NULL
C456	4	A	False	Normal	Normal	FALSE	NULL
C456	5	A	False	Normal	Normal	FALSE	NULL
C456	6	A	False	Normal	Normal	FALSE	NULL
C456	7	A	False	Normal	Normal	FALSE	NULL
C456	8	A	False	Normal	Normal	FALSE	NULL
C456	9	A	False	Normal	Normal	FALSE	NULL
C456	10	A	False	Normal	Normal	FALSE	NULL
C456	11	A	False	Normal	Normal	FALSE	NULL
C456	12	A	False	Normal	Normal	FALSE	NULL
C456	13	A	False	Normal	Normal	FALSE	NULL
C456	14	A	False	Normal	Normal	FALSE	NULL
C456	15	A	False	Normal	Normal	FALSE	NULL
C456	16	A	False	Normal	Normal	FALSE	NULL
C456	17	A	False	Normal	Normal	FALSE	NULL
C456	18	A	False	Normal	Normal	FALSE	NULL
C456	19	A	False	Normal	Normal	FALSE	NULL
C456	20	A	False	Normal	Normal	FALSE	NULL
C456	200	A	False	Normal	Normal	TRUE	COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C456	201	A	False	Normal	Normal	TRUE	COVERAGE_LEVEL_0:On&COVERAGE_LEVEL_1:On&COVERAGE_LEVEL_2:Off
C456	202	A	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

[Anonymous]

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
0	A	False	Normal	Normal	FALSE
1	A	False	Normal	Normal	FALSE
8	A	False	Normal	Normal	FALSE
9	A	False	Normal	Normal	FALSE
10	A	False	Normal	Normal	FALSE
11	A	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
12	A	False	Normal	Normal	FALSE
13	A	False	Normal	Normal	FALSE
19	A	False	Normal	Normal	FALSE
20	A	False	Normal	Normal	FALSE
200	A	False	Normal	Normal	TRUE
201	A	False	Normal	Normal	TRUE
202	A	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
0	A	False	Normal	Normal	FALSE
1	A	False	Normal	Normal	FALSE
2	A	False	Normal	Normal	FALSE
3	A	False	Normal	Normal	FALSE
4	A	False	Normal	Normal	FALSE
5	A	False	Normal	Normal	FALSE
6	A	False	Normal	Normal	FALSE
7	A	False	Normal	Normal	FALSE
8	A	False	Normal	Normal	FALSE
9	A	False	Normal	Normal	FALSE
10	A	False	Normal	Normal	FALSE
11	A	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 · Accepted solution]

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!

[Anonymous]

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]

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!

[v-kelly-msft]

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!