Unanswered

Power automate desktop excel as database

(0) Share

Report

Posted on by venkatesh340789

we have multiple invoice transaction with line type bases of catalog and non catalog item then each catalog and non catalog item have respective invoice line number series, sometime po line number available with similar number to map respective catalog items.

we are looking to get each catalog and non catalog item line update separately and map respective CGST and IGST value in single row.

I have got SQL Code to resolve this issue but getting error will run sql database in power automate desktop

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (0)

Sort by

venkatesh340789 47 on at

Like
a
(0)

Report
Copy link

Link copied!

@UshaJyothi20

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

hi @venkatesh340789
could you please tell me for what you need to write query and please share what query you have written

Was this reply helpful? Yes No
venkatesh340789 47 on at

Like
a
(0)

Report
Copy link

Link copied!

with cte as (
  select *, sum(case when LineType in ('Non-Catalog Item', 'Catalog Item') then 1 else 0 end)
            over (partition by InvoiceID order by InvoiceNumber) as grp,
            case when LineType in ('Non-Catalog Item', 'Catalog Item') then 1 else 0 end as catalog_flag
  from [SQL Test$]
)
select InvoiceID, max(case when catalog_flag = 1 then LineType end) as LineType,
                  max(case when catalog_flag = 1 then InvoiceNumber end) as InvoiceNumber,
                  sum(case when catalog_flag = 1 then sum end ) as Amount,
                  sum(case when LineType = 'IGST' then sum end ) as IGST,
                  sum(case when LineType = 'CGST' then sum end ) as CGST
from cte c
group by InvoiceID, grp

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Do one thing wherever you need to use coloumn name use inside square brackets and inside that coloumn name
Please try it

Was this reply helpful? Yes No
venkatesh340789 47 on at

Like
a
(0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

need to use [column name]
as shown belo
and please share your sql query
after updating [column name]

Was this reply helpful? Yes No

venkatesh340789

47 on at

Like (0)

Report

Copy link

Link copied!

SheetName: SQL Test

I have updated Table Source in Below Table.

InvoiceID	LineType	InvoiceNumber	[PO] Line Type	PO	Sum
000002172352852-611932	Non-Catalog Item	1			51,940.00
000002172352852-611932	CGST	2			0
000002172352852-611932	SGST	3			0
0001-569052	Non-Catalog Item	1			13,41,400.00
0001-569052	IGST	2			0
0001-569052	Non-Catalog Item	3			6,70,700.00
0001-569052	IGST	4			0
0001-569052	Non-Catalog Item	5			2,000.00
0001-569052	IGST	6			0
0001-569052	Non-Catalog Item	7			1,000.00
0001-569052	IGST	8			0
01/2023-24-588032	Non-Catalog Item	1			29,660.00
01/2023-24-588032	IGST	2			5,338.80
01-600214	Non-Catalog Item	1	Non-Catalog Item	1	11,700.00
01-600214	CGST	2	Non-Catalog Item	1	0
050-601011	Non-Catalog Item	1	Non-Catalog Item	1	41,500.00
050-601011	IGST	2	Non-Catalog Item	1	0

Was this reply helpful? Yes No

UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @venkatesh340789
Could you please try the process by removing [] in the header name [PO] Line Type
and then try please

and i am trying to do by using run .net action
once completed i will update you

Was this reply helpful? Yes No
venkatesh340789 47 on at

Like
a
(0)

Report
Copy link

Link copied!

it is not working after removed square brackets.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

SQL queries to Excel are limited in their functionality. You will not be allowed to do CTEs or similar things there. They only support simple queries.

Instead, read the entire table and apply the appropriate filtering in the flow logic, by either using a .NET script, or simply native Excel actions.

Or, alternatively, push the data to an actual database first, and then do proper SQL queries to it.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No