Transformation of Invoice Data

[powerinM]

I would like to do some transformation on the data containg inside Tax invoice. When I connect to the pdf tax invoice I get the data as per Input below. 

First, under the Notes col header all the text needs to combined separated by comma based on the condition in the unit column.

Second, create two custom columns start date e.g 01/11/20118 Finish date e.g 30/11/2018 based on the text in the notes col. Any help would be greatly appreciated. Thanks.

Input:

OutPut

[v-juanli-msft · Accepted solution]

Hi @powerinM 

In Edit queries, click on icon i circled for each step to see how it work, then implement it on your model.

If you have any problem, feel free to let me know,

or you could share a simple example with all your columns' header so to make a test.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[powerinM]

Hi Maggie: Thanks for the solution. I will go through all the steps. Could please ask for two additional custom columns "Start Date" and "Finish Date" and date to be dynamically extracted from the "all" column as per my first post?

[v-juanli-msft]

Hi @powerinM 

Followed your advice, i simplify my previous solution as below:

group rows first, then to extract date columns,

also, it can dynamically get dates as followed this article.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.