cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chandakaushik
Helper I
Helper I

Transforming Billing Feed Data using Power Query

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. 

 

Billing Feed.jpg

 

Desired Output:

Desired Output.jpg

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

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
chandakaushik
Helper I
Helper I

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

mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!