Forum Discussion

gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
9 years ago

Data shaping- source file txt with data in a multirows solution

I am trying to shape some data in a txt file. Unfortunatly data to shape are available on 2-3 rows   I have tried several method but this is the best condition I've got.   To be honest I do ...
Need Help
gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
9 years ago

v-qiuyu-msft

 

 

Hi

below an exmple

https://app.box.com/s/5ejjty321ak1t5s3y8gcqb1dtn3k0txr

 

The txt contains only the headers justo to show what I got. Here we have the headers in 2 rows but the final layout could be 3 rows.

 

I will not prepare the final layout until I'll have some solution to apply.

 

I need to understand the method to shape this kind of file. At the end the method will works with any number of rows

 

This is my expectation:

row 1 "Materiale", "Testo breve Materiale", "Div", "Mag", "TMv", etc, etc...

 

and not like this (output at the moment)

row 1 "Materiale", "Testo breve Materiale", "Div"

row 2 "Mag", "TMv", etc, etc...

 

Thanks in advance

  • gpiero's avatar
    gpiero
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    v-qiuyu-msft

    Hi

    thanks for your support.

    I'll try to study this case.

    In case I need some clarification I'll go on to write on this post.

    • gpiero's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      9 years ago

      v-qiuyu-msft

       

      I have got this

       

      using the following query

       

      let
    Source = Csv.Document(File.Contents("D:\OneDrive - MyCompany\Lavori\01.20_-_POWER BI DATASOURCE\WH Internal Activities\MB51LayOK.TXT"),[Delimiter="|", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type number}, {"Column5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column2] <> "--------------------------------------------" and [Column2] <> "Campo di selezione  " and [Column2] <> "Data di reg.        ")),
    #"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Blank Rows", each ([Column3] = "")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Column5", "Column4", "Column3"})
in
    #"Removed Columns1"

       

      I didn't find a solid solution to mark rows 1 and  2 because they appear at the top of the page.

      I tried to replace rows 3 and 6 with special caracters like "@@" but this does not help me to put an index like the example below, since those rows contain the same string.

       

      At this stage I really need of some precious advices

       

       

      https://community.powerbi.com/t5/Desktop/Power-Query-import-txt-file-with-multiple-lines-per-row-change/td-p/28548