Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Initially I was using the attached sql script to download the data.
But I've dataflows through which I can directly access the data. So would like to convert the sql script to power query to have similar columns.
Please advise.
C:\Users\xv5\Downloads\TREATMENT_LOC NEW.txt
Regards,
Chiru
@chiru5262 , the link is not correct. Please paste the query here.
select distinct loc_dataset.location, 'https://elogin.ads.swc/maximo/ui/?event=loadapp'||'&'||'value=location'||'&'||'additionalevent=useqb... MXE_LINK, loc_dataset.location_desc , loc_dataset.updated_level, loc_dataset.system, loc_dataset.loc_sort, loc_dataset.swclevel, case when loc_dataset.updated_level=1 then loc_dataset.location end as level1, case when loc_dataset.updated_level=2 then loc_dataset.location end as level2, case when loc_dataset.updated_level=3 then loc_dataset.location end as level3, case when loc_dataset.updated_level=4 then loc_dataset.location end as level4, case when loc_dataset.updated_level=5 then loc_dataset.location end as level5, case when loc_dataset.updated_level=6 then loc_dataset.location end as level6, case when loc_dataset.updated_level=7 then loc_dataset.location end as level7, case when loc_dataset.updated_level=8 then loc_dataset.location end as level8, case when loc_dataset.updated_level=9 then loc_dataset.location end as level9, loc_dataset.location_type, loc_dataset.loc_status, loc_dataset.SWCPRODUCT, loc_dataset.SWCOPAREA, (select DESCRIPTION from MAXIMO.SWCHIERARCHY WHERE SWCCODE = SWCOPAREA) OPERAREA_DESC, loc_dataset.SWCOPGROUP, loc_dataset.SWCFACILITY, loc_dataset.Process, loc_dataset.PROCESS_DESC, loc_dataset.FACILITY_DESC, loc_dataset.IS_MTU, (select locationspec.numvalue from maximo.locationspec where loc_dataset.location = locationspec.location and loc_dataset.loc_CLASSSTRUCTUREID = locationspec.classstructureid and locationspec.assetattrid= 'COF_SCORE') COF_SCORE, ESTREPLCOST, LOCPRIORITY, loc_CLASSSTRUCTUREID, classstructure.classificationid, classification.description CLASS_DESC from ( select level as updated_level, lh.systemid as system, SYS_CONNECT_BY_PATH (lh.location, ' \ ') as loc_sort, lpad ('.', (level - 1) * 5,'.') || lh.location as loc_hierarchy, lh.location, l.description as location_desc , lo.swclevel, l.TYPE location_type, l.GLACCOUNT, l.STATUS loc_status, l.STATUSDATE, l.SWCOWNER, l.CLASSSTRUCTUREID loc_CLASSSTRUCTUREID, lo.LOCPRIORITY, lo.SWCPRODUCT, lo.SWCMAINTSTGY , lo.SWCOPAREA, lo.SWCOPGROUP, CASE WHEN lo.SWCFACILITY is null then SUBSTR(lh.location,1,6) else lo.SWCFACILITY end as SWCFACILITY, LEVEL83.LEVEL_83 as Process, LEVEL83.PROCESS_DESC, (SELECT DESCRIPTION FROM MAXIMO.LOCATIONS WHERE lo.SWCFACILITY = LOCATIONS.LOCATION )FACILITY_DESC, CASE WHEN (l.TYPE='FUNCLOC' AND instr(l.LOCATION,'-') <> 7) THEN 1 ELSE 0 END AS IS_MTU, lo.SWCINHERITPARENT , (select LASTREADING from MAXIMO.LOCATIONMETER where l.location=LOCATIONMETER.location and METERNAME= 'ESTREPLCOST')ESTREPLCOST, l.locationsid from maximo.lochierarchy lh inner join maximo.locations l on lh.location = l.location and lh.siteid = l.siteid and lh.SYSTEMID = 'PRIMARY' inner join maximo.locoper lo on lh.location = lo.location and lh.siteid = lo.siteid left outer join (Select PRO.*,LOC.DESCRIPTION as PROCESS_DESC from (select MAXIMO.LOCANCESTOR.LOCATION, MIN(MAXIMO.LOCANCESTOR.ANCESTOR) LEVEL_83 from MAXIMO.LOCANCESTOR, MAXIMO.LOCOPER WHERE MAXIMO.LOCANCESTOR.ANCESTOR=MAXIMO.LOCOPER.LOCATION AND MAXIMO.LOCOPER.SWCLEVEL=83 GROUP BY MAXIMO.LOCANCESTOR.LOCATION) PRO, MAXIMO.LOCATIONS LOC WHERE PRO.LEVEL_83=LOC.LOCATION) LEVEL83 on l.location=LEVEL83.location where l.type in ('FACILITY','FUNCLOC','SYSAREA') AND (l.LOCATION LIKE 'ST%' OR l.LOCATION LIKE 'RT%' OR l.LOCATION LIKE 'WT%' OR l.LOCATION LIKE 'WP%' OR l.LOCATION LIKE 'SP%') AND l.STATUS IN ('EXISTING','PENDINGDECOM','NONMAINTAINED','OPERATING') start with (lo.swclevel = 80 and l.TYPE = 'FACILITY') connect by prior lh.location = lh.parent )loc_dataset Left join maximo.classstructure on loc_dataset.loc_CLASSSTRUCTUREID = classstructure.classstructureid left join maximo.classification on classstructure.classificationid = classification.classificationid order by loc_dataset.location
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |