cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohit139
Helper II
Helper II

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!!

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

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"

 

Works perfectly fine. Thanks a lot. 🙂

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

 

 

 

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 🙂

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

 

 

 

 

View solution in original post

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!!

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!

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.

Error on each 

mohit139_0-1622428152029.png

Sorry i am not that good in M 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors