cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulSGodden
Advocate I
Advocate I

Display first line of a text field in a table

I have a SharePoint list with multiple records that need completing. Each record a Last Update field. This field has multiple lines of text e.g.

 

14/04: Escalated to John. Awaiting a reply.

07/04: Meeting to be arranged to discuss the issue.

31/03: Documentation handed over to Clare to complete OA.

etc., etc.

 

I want to add this field to a table, but only the first line. Any ideas?

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @PaulSGodden ,

 

You could try remove empty feature.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNBTxsxEIX/yihnQjaQ0pZbErigtpFCKw4Rh8Ge7Frx2lt7loV/37ENRA2tgrSKtJuZ53nfPG82o6VvO3TPMB/fTSv41dUBNY1O5LvbmtAiG++AG2RAcDRAIOWDhgYjPBA5QK1JA3upIVDecUDFsDZxB2uqTWQKonbjGwe3reFGXqazSTW7hOuo0CKX7u/osKYAc0Cn4crIMewDLETWRBiIdqej+5PNSNrLc/Z1Up1fwnxAw8bV0gZ9p0XuoG568bG6apbrfhK28I2EQUhjYQjoahLvMquFQQzA3kuqqInLjIGit4+ki+4r1sX4lsKjUQTXT4YPuQb63YtVXQAngsZtA0YOveI+0B6zFuytiVG6SCeg6AiufNpUdT6pPn3E4UXG/kp6ATJ07XPHgM/Ji/V+JxMUV1nrH84ON7AWDxTTGo+s4HhhdZYLszcZIqUphelgJ4k4Qhd8LYPFF6W/oC/H866zRhXI/8n0e/ar1ertTCvJ3dMvZyTuS4uylhvZQkwIvmSkx0NYEl+ak6rkyVtLWPfZaWfUrrDuu3wBIkkUthDTnRLRZNmw9LSdJX63iennzO2HZ4iMISOO4HxxQU/yG2Er1ykfoawhx6dwZyTRIknwgHJ8+v9tfkmBzNgKjCYCmzYZuv8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer-Service" = _t, Action = _t, Owner = _t, #"Last Update" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer-Service", type text}, {"Action", type text}, {"Owner", type text}, {"Last Update", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"Customer-Service"] <> null and [#"Customer-Service"] <> "")
in
    #"Filtered Rows"

vjaywmsft_0-1652261330866.png

vjaywmsft_1-1652261340864.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

Hi @PaulSGodden ,

 

You could try remove empty feature.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNBTxsxEIX/yihnQjaQ0pZbErigtpFCKw4Rh8Ge7Frx2lt7loV/37ENRA2tgrSKtJuZ53nfPG82o6VvO3TPMB/fTSv41dUBNY1O5LvbmtAiG++AG2RAcDRAIOWDhgYjPBA5QK1JA3upIVDecUDFsDZxB2uqTWQKonbjGwe3reFGXqazSTW7hOuo0CKX7u/osKYAc0Cn4crIMewDLETWRBiIdqej+5PNSNrLc/Z1Up1fwnxAw8bV0gZ9p0XuoG568bG6apbrfhK28I2EQUhjYQjoahLvMquFQQzA3kuqqInLjIGit4+ki+4r1sX4lsKjUQTXT4YPuQb63YtVXQAngsZtA0YOveI+0B6zFuytiVG6SCeg6AiufNpUdT6pPn3E4UXG/kp6ATJ07XPHgM/Ji/V+JxMUV1nrH84ON7AWDxTTGo+s4HhhdZYLszcZIqUphelgJ4k4Qhd8LYPFF6W/oC/H866zRhXI/8n0e/ar1ertTCvJ3dMvZyTuS4uylhvZQkwIvmSkx0NYEl+ak6rkyVtLWPfZaWfUrrDuu3wBIkkUthDTnRLRZNmw9LSdJX63iennzO2HZ4iMISOO4HxxQU/yG2Er1ykfoawhx6dwZyTRIknwgHJ8+v9tfkmBzNgKjCYCmzYZuv8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer-Service" = _t, Action = _t, Owner = _t, #"Last Update" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer-Service", type text}, {"Action", type text}, {"Owner", type text}, {"Last Update", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"Customer-Service"] <> null and [#"Customer-Service"] <> "")
in
    #"Filtered Rows"

vjaywmsft_0-1652261330866.png

vjaywmsft_1-1652261340864.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thanks @v-jayw-msft for sending this through. However the individual updates are in the same field e.g.

 

 Customer-ServiceActionOwnerLast Update
1Company A-W10 UpgradeConfirmation that a new record has been added to the contract Risk RegisterJohn Smith14/04: Escalated to Manager A and Director B this week.
29/03: Awaiting an update.
16/03: Awaiting an update.
04/03: Team Leader to arrange a call with John Smith to get this resolved.
2Company B-Service ExitConfirmation required that the infrastructure has been decomissionedJane Doe03/05: Awaiting an update.
06/04: Manager B is going away to look into getting this resolved.
29/03: Requested an update.
16/03: Requested an update.
02/03: Jane to contact Team Leader to get a progress update
3Company C-Application UpgradeConfirmation required that the OOO contact list has been updatedClare Jones28/04: Awaiting an update.
14/04: Clare has a colleague to pick this up and see if she can get it completed.
17/03: Not started as no list exists for this client. Will come back for an update in a months time.

 

Am guessing that this will affect the suggested solution?

 

Regards

 

Paul

speedramps
Super User
Super User

Each row is a  "record".

I assume that you mean each "ticket" has multiple rows and you just want the most recently dated record per ticket?

 

Please provide example of source date .... but hide private data.

 

Paste the data as table and not a screen print, then we can use it to develop a solution. Thanks !

Customer-ServiceActionOwnerLast Update
Company A-W10 UpgradeConfirmation that a new record has been added to the contract Risk RegisterJohn Smith14/04: Escalated to Manager A and Director B this week.
29/03: Awaiting an update.
16/03: Awaiting an update.
04/03: Team Leader to arrange a call with John Smith to get this resolved.
Company B-Service ExitConfirmation required that the infrastructure has been decomissionedJane Doe03/05: Awaiting an update.
06/04: Manager B is going away to look into getting this resolved.
29/03: Requested an update.
16/03: Requested an update.
02/03: Jane to contact Team Leader to get a progress update
Company C-Application UpgradeConfirmation required that the OOO contact list has been updatedClare Jones28/04: Awaiting an update.
14/04: Clare has a colleague to pick this up and see if she can get it completed.
17/03: Not started as no list exists for this client. Will come back for an update in a months time.

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.