Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Input
Quantity | ID |
400,1,81,A,, 400,82,93,S14,78, 400,94,96,A,, | 12345 |
Output
Quantity | ID | A | S14 | Total for A | Total for S14 |
400,1,81,A,, 400,82,93,S14,78, 400,94,96,A,, | 12345 | 81, 96 | 93 | 84 | 12 |
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
Solved! Go to 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
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))
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
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
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
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
Sorry i am not that good in M