Transforming Billing Feed Data using Power Query

[chandakaushik]

Hello Power Users,

I get an automated billing feed via email for various insurance products in the following format. I was just wondering if someone can help me transform it into a table please. 

Desired Output:

@stevedep @v-kelly-msft @edhans @mahoneypat @Anonymous 

[chandakaushik]

@mahoneypat  Thanks Pat! This was really helpful! Appreciate your help!! 🙂 

[mahoneypat · Accepted solution]

You can adapt the approach shown in this video to get your desired result.  You will need to add a custom column for each of the fields above the table with a formula like this

= if Text.Contains([Column1], "GROUP NO") then [Column1] else null

Once you do that for each, you can use Fill Down to populate those columns, then filter out the original header rows.

(3) Power BI - Shift N Fill Data Pattern - YouTube

Pat