Thanks for the info David, I have cobbled together a rough solution to get us live, as per below.

cheers, Ravi

--1. insert distinct list of ppsrk into temp table

IF OBJECT_ID('tempdb..#temp_EDI_WC_Error_IDs') IS NOT NULL
DROP TABLE #temp_EDI_WC_Error_IDs;

select distinct ZMPPSRK into #temp_EDI_WC_Error_IDs
from JDE_DEVELOPMENT.testDTA.F01131
join JDE_DEVELOPMENT.testDTA.F01131M on ZZSERK=ZMPPSRK
where Zzdti>=((DATEPART(yy, getdate()) - 1900) * 1000 + DATEPART(dy, getdate())) -20
and (ZZTSV like '%R47500%' or ZZTSV like '%R47131%' )
and (ZMMSGP like '%Error%' or ZMTSV like '%|SO|%')

--2. insert distinct list of errors into temp error table

IF OBJECT_ID('tempdb..#temp_EDI_WC_Errors') IS NOT NULL
DROP TABLE #temp_EDI_WC_Errors;

select distinct zmmsgp as [Error], ZMPPSRK as [Error ID], FORMAT(DATEADD(dd, (ZMDTI % 1000) - 1, DATEADD(yy, ZMDTI / 1000, 0)), 'd', 'en-gb' ) as [Run Date]
into #temp_EDI_WC_Errors from JDE_DEVELOPMENT.testDTA.F01131M where ZMPPSRK in (select ZMPPSRK from #temp_EDI_WC_Error_IDs) and zmmsgp <>''

insert into #temp_EDI_WC_Errors ([Error], [Error ID],[Run Date])
select distinct ZMTSV as [Error], ZMPPSRK as [Error ID], FORMAT(DATEADD(dd, (ZMDTI % 1000) - 1, DATEADD(yy, ZMDTI / 1000, 0)), 'd', 'en-gb' ) as [Run Date]
from JDE_DEVELOPMENT.testDTA.F01131M where ZMPPSRK in (select ZMPPSRK from #temp_EDI_WC_Error_IDs) and ZMTSV <>''

--3.select for PowerBI
select * from #temp_EDI_WC_Errors order by [Error ID] desc