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
v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi gpiero,

 

In Power BI Query Editor, there are some features we can use to transform data. See: Common query tasks in Power BI Desktop

 

In your scenario, it would be better you can share the sample source data and expected results for our analysis.

 

Best Regards,
Qiuyun Yu

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