I have a SQL sourced dataset with the following format that needs to be parced, re-structured, and have duplicates removed. I don't have access to change the SQL server, and I would prefer to keep this in PowerApps instead of setting up a flow.
Problem: "Heat No": output is being stored as a column of tables instead of a column of values. This is also getting pretty messy and I keep wondering if there is a more elegent way (such as ForAll?) to do this transformation.
Incoming Format:
| Description | Planner | Job | … |
| ** HEAT NUMBER 1235 ** | A | 3 | |
| ** HEAT NUMBER 1236 ** CCP ** | B | 5 | |
| ** HEAT NUMBER 1237 ** | B | 7 | |
| ** HEAT NUMBER 1237 ** | B | 9 | |
Target Format:
| Heat Number | Planner |
| 1235 | A |
| 1236 | B |
| 1237 | B |
Current Code:
//Copy SQL to buffer
ClearCollect(DataImport_JobListBuffer,'[MFGHEAT].[WIPDATA]'); //Tried to filter this directly without success. Seems you need to buffer data in
//Filter buffer
ClearCollect(DataImport_HeatList,Filter(DataImport_JobListBuffer,'A$Organization_Code'="NWT",ITEM_PLANNER_CODE<>"NWT3"));
//Parse out heat number
ClearCollect(DataImport_HeatList2,Substitute(DataImport_HeatList.Description,"**HEAT NUMBER",""));
ClearCollect(DataImport_HeatList2,Substitute(DataImport_HeatList2.Description,"**CPP**",""));
ClearCollect(DataImport_HeatList2,Substitute(DataImport_HeatList2.Description,"**CRP**",""));
ClearCollect(DataImport_HeatList2,Substitute(DataImport_HeatList2.Description,"**",""));
//Add columns back in
ClearCollect(DataImport_HeatList3,AddColumns(DataImport_HeatList,"Heat No",DataImport_HeatList2.Description));
//Remove duplicates
//ClearCollect(DataImport_HeatList4,Distinct(DataImport_HeatList3,Heat No));
