Convert specific rows into column on Power Query

[Anonymous]

Hello,

Can I convert only row with "0102931 - BRENO CARDOZO PEREIRA" into a new column from row 170 until row 173 and then convert row " 0103255 - RONILSON DE OLIVEIRA" into a new column and then go on with other next names?

As Is:

To be:

Thanks

[ricardocamargos · Accepted solution]

Hi @Anonymous,

I tried to reproduce your scenario, here what I have done after promove the headers:

   Change the type of the "HORÁRIO" column to text;

   Split the "DATA" column using position (10):

= Table.SplitColumn(#"Promoted Headers", "DATA", Splitter.SplitTextByPositions({0, 10}, false), {"DATA.1", "DATA.2"})

  Split the "Horário" column using delimiter:

= Table.SplitColumn(#"Changed Type2", "HORÁRIO", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"HORÁRIO.1", "HORÁRIO.2"})

Fill down the "Horário.2"

Split the "Horário.1.2" column using positions (8)

= Table.SplitColumn(#"Filled Down", "HORÁRIO.1", Splitter.SplitTextByPositions({0, 8}, true), {"HORÁRIO.1.1", "HORÁRIO.1.2"})

Now just filter the dataset to exclude the rows which contains "Funcionario" or "Nome"

I hope it helps you,

Ricardo