Separating values from columns where there are multiple attribute values in a single column

[ctho222]

SO! I was given a log document that staff in a different medical unit input various metrics they want to observe[see below example of the LOG]. I was told to smoothen the user experience of inputting data and also make it easier to transpose the data into a table. My problem is that the key column in the RESULTANT table isn't a unique identifier of each case [DATE], and the LOG 'table' has multiple different attributes all living in a single column. Rather than reinvent the wheel because I don't have a lot of company time, I wanted to ask if there was a simple way in PowerQuery to extract the attributes, make them headers in a table structured like the one the team currently uses, and then match up the values based on whatever makes it all work? I had made  formulas that dynamically pulls the needed values from the LOG tab based on cell position/structure of the LOG, but they're pretty iterative and as a consequence, cheugy [see below image].

If I had my druthers, I would tell the staff to input the observed info into something computers AND humans like reading, like a table or an app that could reduce input time and structure the data. But I don't work in that area and the staff are used to this habit, so it would be best just to coopt the current process and make the robots sweat data manipulation tears.

Any theories are appreciated, but no solution emergently needed. Thank you and have a good day!

DATE	1/2/2025	RUN #	XX-XXXXX	MR #	XXX
DX	Mehvirus	NAME	Turner, Joe
Ref. Hosp.	Baptist Health	DOB	01/01/1969
Ref. MD	King	REF. UNIT	ED	INFO:		TYPE	INTERFACILITY
ATN	MCCOY	RN	TURNER			PATIENT	PEDIATRIC
DVR/Pilot	SMITH	Other				TEAM	DAY
RCVD Call	12:00 AM	Dispatch	1:30 AM	Rec. Hosp.	CBGB
ED/Helipad	06:10	Enroute	06:13	Rec. MD	Pradhu	REC. UNIT	ER
Arr. Ref.	07:31	07:33	Dep. Ref.	07:50	07:55	Arr. Rec.	09:17	METHOD	AMBULANCE

EX. Pull date from LOG tab[=IFERROR(INDIRECT(IF(ROW(A2)=2,"LOG!$B$1","LOG!B"&(((ROW(A2)-2)*10)+1))),"Oops, something looks off, is your log info correct?")]

EX. Pull hospital arrival time from log and format =IFERROR(IF(ROW(X2)=2,IF(ISNUMBER(SEARCH(":", INDIRECT("LOG!$B$9"))),TEXT(TIME(LEFT(INDIRECT("LOG!$B$9"), FIND(":", INDIRECT("LOG!$B$9"))-1), MID(INDIRECT("LOG!$B$9"), FIND(":", INDIRECT("LOG!$B$9"))+1, LEN(INDIRECT("LOG!$B$9"))), 0), "hh:mm"),TEXT(INT(INDIRECT("LOG!$B$9")/100), "00") & ":" & TEXT(MOD(INDIRECT("LOG!$B$9"), 100), "00")),IF(ISNUMBER(SEARCH(":", INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)))),TEXT(TIME(LEFT(INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)), FIND(":", INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)))-1), MID(INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)), FIND(":", INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)))+1, LEN(INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)))), 0), "hh:mm"),TEXT(INT(INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9))/100), "00") & ":" & TEXT(MOD(INDIRECT("LOG!B" & (((ROW(X2)-2)*10)+9)), 100), "00"))), "Something seems to have gone wrong, make sure to check formatting type in the LOG")

[jgeddes · Accepted solution]

If I can assume that the Log Headers are the same from log to log then you code do something like this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVJdb4IwFP0rjXt1fBk18lbaOtikkK4YjfGBaDdIiBDA/f65vfQya4wjhNx7OLfn3tuz240olmy0H+9Gvx/X9mzP8aYaQToUGUdPOt1snjc/j5EbiyEVsCiIY1V8le25M55xN+Q4Br3Lc3tS7Ri91gr0rD4sFNZdY2ksyJu+7HoUqrzqi0cEaRLoxHHty+suZos/cjHVwFt5+jQvky0tlPFIaohRIzPiy8Q3/pHblEGeZGKJSbSK5FbDWHKwb0KSrbkfwJKZ4Ew8sJcUy4hxMErKaISliAjY3VrYaVnVvYbe40iGxgOTvlDt/0whGY6BKgbjCrKmiORVBQzv+Y6DYAW87rJr8v5QALo/uckW6nBlNRK8BPcaZtQOVVU2+RF4a+a7jpl9autzr4bUye2GoBnTNj8WZ+hBcuVBcO24bS3042ggNvcn7jAF2lQ1hoKpM0ynVwoHWLDw3TkwLJNhAkbAcZCtMCcX3++/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    header_list = {"DATE", "DX", "EF. HOSP.", "REF. MD", "ATN", "DVR/PILOT", "RCVD CALL", "ED/HELIPAD", "ARR. REF.", "RUN #", "NAME", "DOB", "REF. UNIT", "RN", "OTHER", "DISPATCH", "ENROUTE", "DEP. REF.", "MR #", "INFO:", "REC. HOSP.", "REC. MD", "TYPE", "PATIENT", "TEAM", "REC. UNIT", "ARR. REC.", "METHOD", "REF. HOSP."},
    remove_blank_rows = Table.SelectRows(Source, each ([Column1] <> "" and [Column1] <> " ")),
    add_isHeader_column = Table.AddColumn(remove_blank_rows, "isHeader", each if List.Contains(header_list, [Column1], Comparer.OrdinalIgnoreCase) then [Column1] else null, type text),
    fill_down_isHeader = Table.FillDown(add_isHeader_column,{"isHeader"}),
    group_by_isHeader = Table.Group(fill_down_isHeader, {"isHeader"}, {{"All Rows", each _, type table [Column1=nullable text, isHeader=text]}}),
    select_nested_table_values = Table.TransformColumns(group_by_isHeader, {{"All Rows", each Table.SelectColumns(Table.SelectRows(_, each [Column1] <> [isHeader]), {"Column1"})}}),
    add_nested_index = Table.TransformColumns(select_nested_table_values, {{"All Rows", each Table.AddIndexColumn(_, "Index", 1, 1)}}),
    expand_nested_table = Table.ExpandTableColumn(add_nested_index, "All Rows", {"Column1", "Index"}),
    pivot_table = Table.Pivot(expand_nested_table, List.Distinct(expand_nested_table[isHeader]), "isHeader", "Column1"),
    remove_null_index = Table.SelectRows(pivot_table, each ([Index] <> null)),
    remove_index_column = Table.RemoveColumns(remove_null_index,{"Index"}),
    set_data_types = Table.TransformColumnTypes(remove_index_column,{{"DATE", type date}, {"RUN #", type text}, {"MR #", type text}, {"DX", type text}, {"NAME", type text}, {"Ref. Hosp.", type text}, {"DOB", type date}, {"Ref. MD", type text}, {"REF. UNIT", type text}, {"INFO:", type any}, {"TYPE", type text}, {"ATN", type text}, {"RN", type text}, {"PATIENT", type text}, {"DVR/Pilot", type text}, {"Other", type any}, {"TEAM", type text}, {"RCVD Call", type time}, {"Dispatch", type time}, {"Rec. Hosp.", type text}, {"ED/Helipad", type time}, {"Enroute", type time}, {"Rec. MD", type text}, {"REC. UNIT", type text}, {"Arr. Ref.", type time}, {"Dep. Ref.", type time}, {"Arr. Rec.", type time}, {"METHOD", type text}})
in
    set_data_types

The basic idea is to set a list of the headers, compare the existing column to the header list, group by header names, select the coresponding values, pivot the table to end up with...

Hope this helps.

[ctho222]

Works for me and now the spreadsheet doesn't chug when it runs thank ya kindly!

[ctho222]

DATE

RUN #

MR #

DX

NAME

DOB

REF HOSP

REF MD

REC HOSP

REC UNIT

REC MD

TYPE

METHOD

PATIENT

TEAM

RN 1

RN 2

DVR/PILOT

OTHER

RVCD

DISP

ED/HELIPAD

ENRT

ARR HOSP

ARR PT

DEP PT

DEP HOSP

ARR REC

ISSUES

Call to enroute

Dispatch to enroute

Bedside time

Total transport time

1/2/2025

XXX

XXX

Mehvirus

HR start at 1AM, HR increment an hour

12/5/2024

Baptist Health

King

CBGB

ED

Pradhu

INTERFACILITY

AMBULANCE

PEDIATRIC

DAY

MCCOY

TURNER

SMITH

0

01:00

02:00

03:00

04:00

05:00

06:00

07:00

08:00

09:00

0

1:00

2:00

1:00

8:00