Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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!
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. |
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!
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.
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!
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!