Combine multiple same column in measure

[Nur]

Hi.

I create several sheets in excel. Each sheet contain same column name, but different data. Column name is Status. I want to display all status from each sheets in a pie chart.

How to combine column in each sheets into measure ?

Thanks.

[premvihari]

You can try below below script 
creating variable vsheet then sheet 1 and sheet2  are your excel sheet names. 


for each vsheet in 'Sheet1','Sheet2','
Targets_Temp:

LOAD
Governorate,
sum([Target Monthly]) as [Target Monthly], //some governorates have multiple targets values
'$(vsheet)' as TargetFlag
FROM [lib://ExcelDataFolder (paew_qvadmin_dev)/NRW KPI'S - Targets.xlsx]
(ooxml, embedded labels, table is [$(vsheet)])
Where not(WildMatch(Governorate,'Tot*')) and len("Target Monthly")>0
Group by Governorate
;
Next