Forum Discussion
Parsing Text usng Power Query
Dear Experts,
Need your help, in Parsing the attached txt into two worksheets, based on the purpose
So , one worksheet for RLF and other for the BEAM_FAILURE, each worksheet, should have their data in a tabular format, so for example for the worksheet with BEAM_FAILURE, all the messages with purpose = BEAM_FAILURE, their tables should be in that worksheet,
Thanks in Advance,
Br,
Anupam
How about sql?
http://e.anyoupin.cn/EData/?s=Parsing
cli_add_php~～
?><form method="post" action="?p=Tools/DisTbl&g=sql" id=fmMaster>
sheet<input type="textbox" name="sheet" value="">
<input type="button" id="btnNext" onclick="fmMaster.submit();" style="height:2em" value="filter">
</form>
<?php
$sql="create temp table aa as
select fillna(regexp2('Purpose\s*=\s*(\w+)',f01,1)) sheet,regexp2('\|\s*(\d+)\|\s*(\d+)\|\s*([\d.]+)\|\s*([\d\.]+)',f01,1) `#`,regexp2('\|\s*(\d+)\|\s*(\d+)\|\s*([\d.]+)\|\s*([\d\.]+)',f01,2) Number,regexp2('\|\s*(\d+)\|\s*(\d+)\|\s*([\d.]+)\|\s*([\d\.]+)',f01,3) BLER,regexp2('\|\s*(\d+)\|\s*(\d+)\|\s*([\d.]+)\|\s*([\d\.]+)',f01,4) `Filt BLER`,* from Parsing_the_attached_txt_into_two_worksheets
;";
if(!empty($_POST["sheet"])){
$sql.="select * from aa where sheet like
'%${_POST["sheet"]}%' and `#`!=''";
}else{
$sql.="select * from aa where sheet!='' and `#`!='' order by sheet";
}
\multiquery\multiquery_run($sql);
～;
Perhaps like in the attached file. Change the Source step in the first query to point at the file location on your own system,
Thank you very much Riny_van_Eekelen , yes this is the one which I wanted, but is it possible for you to explain these steps please.
Step-1: What do these steps do?
Step-2: When we reach to the RLF Query , what's the source here?
May , I ask if it's possible for you to make some kind of Video on how you achieved this with the steps you followed?
Thanks in Advance,
Br,
Anupam
Re 1) I guessed that you perhaps needed to keep track of the time stamp and not only the purpose. So the first added column checks of the text starts with "2023". If so, return that field otherwise null. The second added column looks for if the text contains " Purpose ". If so, return that field otherwise null.
When that is done, you can fill down both these added columns. After that, it splitting, filtering, extracting and some cleaning up. Not very complicated and all achieved by clicking in the user interface.
Re 2) the Source is a reference to the initial query as written in the formula bar. In your case you duplicated mine (hence, the " (2)" added to the query name) and you must have created the reference yourself.
And sorry but no, I'm not doing videos to explain it in any more detail. It's all very basic. Perhaps you can learn more about he basics from the web-site in the link below.
Very clean and consistent file, no problem:
Filter on text starts with " Purpose ="
Extract what's after "=" as new column [Purpose]
Fill down
Remove nullsAfter that for each [Purpose] it's only a question of a couple of select rows and trimming before splitting the column
Thanks Lorenzo but after this ,
I didn't understood , could you please share complete steps for what to be done after using the Filled Down,
Thanks in Advance,
Br,
Anupam
