Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query, Extract Text before Text

Hi,

 

Input

QuantityID
400,1,81,A,,
400,82,93,S14,78,
400,94,96,A,,
12345

 

Output

QuantityIDAS14Total for ATotal for S14
400,1,81,A,,
400,82,93,S14,78,
400,94,96,A,,
1234581, 96938412
    calc = ((81-1)+1 ) + ((96-94)+1)calc = (93-82)+1

In the table above i have a requirement where i should get whenever it occurs A give me the previous value which is basically 81, then again if occurs S14 give me 93 in the next col, Similary again for last A give me 96. Then calculate the total intervals between A, S14. So for A 1 to 81 + 94 to 96 = 84 

For s14 = 82 to 93  = 12 intervals.

 

Any help appreciated.

 

Thank you

1 ACCEPTED SOLUTION

Well, your initial source table was kinda confusing. 

This should work.

 

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcsxEoAwCETRu1BvERIgUHoGy5greP/S4MTCilnm/TFISgHDGQdw3bm8IhpOFnTfrxCEpSAQ1ya6bhhNfL3aP9D+Boults3nAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quantity = _t, ID = _t, Total = _t]),
    SumA = Table.AddColumn(Source, "Total A", each let l=Text.Split([Quantity], ","), i= List.PositionOf(l, "A", Occurrence.All ) in  List.Sum(List.Transform(i, each Number.FromText(l{_-1})-Number.FromText(l{_-2}) +1))),
    SumS14 = Table.AddColumn(SumA, "Total S14", each let l=Text.Split([Quantity], ","), i= List.PositionOf(l, "S14", Occurrence.All ) in  List.Sum(List.Transform(i, each Number.FromText(l{_-1})-Number.FromText(l{_-2}) +1)))
in
    SumS14

 

 

 

 

View solution in original post

15 REPLIES 15
watkinnc
Super User
Super User

Sorry, too many parentheses at the end of these-- fixed it:


FirstA1 = Table.AddColumn(PreviousStepName, "FirstA1", each let items = List.PositionOf(Text.Split([Quantity], ","), "A") in List.Last(List.FirstN(Text.Split([Quantity], ","), items)),

S12 = Table.AddColumn(FirstA1, "S12",  each let items = List.PositionOf(Text.Split([Quantity], ","), "S12") in List.Last(List.FirstN(Text.Split([Quantity], ","), items)),

SecondA = Table.AddColumn(S12, "SecondA", each let items = List.PositionOf(Text.Split([Quantity], ","), "A", Occurrence.Last) in List.Last(List.FirstN(Text.Split([Quantity], ","), items))


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

Apologies, but it doesnt work for me and throws errors. can i please request you to create a PBIX file and attach may be?

 

This is giving me headache from last 3 days. 

Hi

 

Try this in blank query and adjust accordingly.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEw0DHUsTDUcdTRUdJRUorVgYhZGOlYGusEG5romFugSFia6FiaQVUbGhmbmCrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quantity = _t, ID = _t]),
    PosA = Table.AddColumn(Source, "A", each try let i = List.PositionOf(Text.Split([Quantity], ","), "A", Occurrence.Last) in Number.FromText(Text.Split([Quantity], ","){i-1}) - Number.FromText(Text.Split([Quantity], ","){i-2})+1 otherwise 0),
    PosS14 = Table.AddColumn(PosA, "S14", each try let i = List.PositionOf(Text.Split([Quantity], ","), "S14", Occurrence.Last) in Number.FromText(Text.Split([Quantity], ","){i-1}) - Number.FromText(Text.Split([Quantity], ","){i-2})+1 otherwise 0),
    A = List.Sum(PosS14[A]),
    S14 = List.Sum(PosS14[S14])
in
    S14
Anonymous
Not applicable

Hi Jakinta,

How will similar information be processed just to retrieve 
A,F19,F17,S19 from below TEXT field below using power query?

300,1,1,A,,
300,1,63,F19,37,
300,64,67,F17,76,
300,68,96,S19,76,


Thanks in advance!

I hope this is what you want.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYw0DEEQkcdnZg8CMfMWMfN0FLH2BwqYmaiY2YOFDLXMTeDCVnoWJrpBANVAYWUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.Split( [Column1], ","), each if Value.Is(Value.FromText(_), type text) and  _<>"" then _ else null)),","))
in
    #"Added Custom"

 

Anonymous
Not applicable

Works perfectly fine. Thanks a lot. 🙂

Anonymous
Not applicable

Hi @Jakinta , You have been great help Thanks a lot.

 

Apologies but i have come up with another hurdle in my report and need your help : )

 

Something like below - first col is input col (everything is dynamic - basically if a value in col 1 has appeared say "A" unless it is interrputed by any other value say "S14" i should get its distinct (only once) and if it occurs again after "S14" then i should get that and in seq. Example is below) and second col is the output format i need. Again any help much appreciated.

 

Quality (Input col)                                                                                                                       
["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14"]         
["A", "K05, "A", "S14", "S14", "S14", "S14", "S14", "K05", "S14", "S14", "S14", "S14", "S14"]         
["A", "A", "A", "S14", "A", "S14", "S14", "S45", "S14", "S14", "S14", "A", "K05", "A"]                     
["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "A"]               


Distinct Sequential Quality (Output col) - corrsponding to the above rows. (sorry it wont let me paste in the table format)
A, S14
A, K05 , A, S14, K05, S14
A,S14, A, S14, S45, S14,A,K05,A
A,S14,A

 

 

 

Anonymous
Not applicable

This works completly fine. Thank you very much. Although, i dont understand what's going on cuz i tried to split the steps into different columns but get error in the third step. However together everything works fine. Thank you 🙂

Anonymous
Not applicable

Jakinta, This is very close to what i want. Thank you. But i need a little more help. In the input below i have only 2 rows. first with 3 400 lines and 2nd with 2 400 lines. So in my output as well i need only 2 output lines otherwise it messes up mt "Total" column as i need to aggregate it in my measure calculation.  Again, i appreciate the help. 

 

Input
Quantity                    ID                   Total
"400,1,81,A,,
400,82,93,S14,78,
400,94,96,A,,"           12345                 96


"400,1,56,S14,78,
400,57,96,A"            23456                 96
Output
Quantity                           ID                  Total           Total A           Total S14
"400,1,81,A,,
400,82,93,S14,78,
400,94,96,A,,"               12345                   96             84            12


"400,1,56,S14,78,
400,57,96,A"                  23456                   96            40             56

 

 

P.S: My quantity values are dynamic. There can be any number of 400 lines. So the occurences of A,S14 are dynamic in number too

Well, your initial source table was kinda confusing. 

This should work.

 

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcsxEoAwCETRu1BvERIgUHoGy5greP/S4MTCilnm/TFISgHDGQdw3bm8IhpOFnTfrxCEpSAQ1ya6bhhNfL3aP9D+Boults3nAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quantity = _t, ID = _t, Total = _t]),
    SumA = Table.AddColumn(Source, "Total A", each let l=Text.Split([Quantity], ","), i= List.PositionOf(l, "A", Occurrence.All ) in  List.Sum(List.Transform(i, each Number.FromText(l{_-1})-Number.FromText(l{_-2}) +1))),
    SumS14 = Table.AddColumn(SumA, "Total S14", each let l=Text.Split([Quantity], ","), i= List.PositionOf(l, "S14", Occurrence.All ) in  List.Sum(List.Transform(i, each Number.FromText(l{_-1})-Number.FromText(l{_-2}) +1)))
in
    SumS14

 

 

 

 

watkinnc
Super User
Super User

My fault--add "each" after the "in"s:

 

FirstA1 = Table.AddColumn(PreviousStepName, "FirstA1", each let items = List.PositionOf(Text.Split([Quantity], ","), "A") in each List.Last(List.FirstN(Text.Split([Quantity], ","), items))),

S12 = Table.AddColumn(FirstA1, "S12",  each let items = List.PositionOf(Text.Split([Quantity], ","), "S12") in each List.Last(List.FirstN(Text.Split([Quantity], ","), items))),

SecondA = Table.AddColumn(S12, "SecondA", each let items = List.PositionOf(Text.Split([Quantity], ","), "A", Occurrence.Last) in each List.Last(List.FirstN(Text.Split([Quantity], ","), items)))

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

Try:

FirstA1 = Table.AddColumn(PreviousStepName, "FirstA1", each let items = List.PositionOf(Text.Split([Quantity], ","), "A") in List.Last(List.FirstN(Text.Split([Quantity], ","), items))),

S12 = Table.AddColumn(FirstA1, "S12",  each let items = List.PositionOf(Text.Split([Quantity], ","), "S12") in List.Last(List.FirstN(Text.Split([Quantity], ","), items))),

SecondA = Table.AddColumn(S12, "SecondA", each let items = List.PositionOf(Text.Split([Quantity], ","), "A", Occurrence.Last) in List.Last(List.FirstN(Text.Split([Quantity], ","), items)))

 

This will get your values lined up in columns. I'll leave the arithmetic to you! 🙂

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

Hi Nate,

How will similar information be processed just to retrieve A,F19,F17,S19 from below TEXT field
300,1,1,A,,
300,1,63,F19,37,
300,64,67,F17,76,
300,68,96,S19,76,
Thanks in advance!

Anonymous
Not applicable

Thanks for the reply 🙂. But i get the error. Expression.Error: We cannot convert a value of type Record to type Text. while expanding the column.

 

Not sure why. I have the column as Text Data type only.

Anonymous
Not applicable

Error on each 

mohit139_0-1622428152029.png

Sorry i am not that good in M 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors