Forum Discussion
Help required to separate the text
Thanks dufoq3
This step is pulling null in the "GroupHelper" column and because of that, description gets combined for all those lines. I have attached screenshot of those lines. Thanks!
Table.AddColumn(#"Filtered Rows", "GroupHelper", each if (try Text.Range([Description], 2, 1) otherwise null) = "-" then [Index] else null, type text)
I assumed that this behavior is ok. Could you provide correct result of such lines pls?
- vineshparekh2 years agoHelper I
Hi dufoq3
Thank you! I am sharing the actual data and the results with the queries.
Source Data: (Two Months)
Source.Name Description Column2 Utilisation Column4 Rate Amount Dec23-555.pdf LG-OH - LG-OH - Landing Gear Leg Overhaul - [SN: 190555] -Correction from period 01-Dec-2023 to 31-Dec-2023 1.00 2652 2652 Dec23-555.pdf AF-192M - Airframe 192 Month Structural Check - [SN: 190555] -Correction from period 01-Dec-2023 to 31-Dec-2023 1.00 610 610 Dec23-555.pdf AF-BC - Airframe Basic Check - [SN: 190555] - Correction from
period 01-Dec-2023 to 31-Dec-2023260.23 37 9628.51 Dec23-555.pdf AF-96M - Airframe 96 Month Structural Check - [SN: 190555] -Correction from period 01-Dec-2023 to 31-Dec-2023 1.00 702 702 Dec23-555.pdf AF-20KC - Airframe 20,000 FC Structural Check - [SN: 190555] - 99.00 4.4 435.6 Dec23-555.pdf Correction from period 01-Dec-2023 to 31-Dec-2023 Dec23-555.pdf AP-OH - AP-OH - APU Overhaul - [SN: PWC-ZD0103] - Correction
from period 01-Dec-2023 to 31-Dec-202390.30 25 2257.5 Dec23-555.pdf - [SN: EN-LP - EN-LP - Engine LLP Replacement
P783246]Correction from period 01-Dec-2023 to 31-Dec-202399.00 162.08 16045.92 Dec23-555.pdf - [SN: EN-PR - EN-PR - Engine Full Performance Restoration
P783246] - Correction from period 01-Dec-2023 to 31-Dec-2023260.23 135 35131.05 Dec23-555.pdf - [SN: P783255] EN-LP - EN-LP - Engine LLP Replacement
Correction from period 01-Dec-2023 to 31-Dec-202399.00 162.08 16045.92 Dec23-555.pdf - [SN: EN-PR - EN-PR - Engine Full Performance Restoration
P783255] - Correction from period 01-Dec-2023 to 31-Dec-2023260.23 135 35131.05 Dec23-555.pdf Nov23-555.pdf LG-OH - LG-OH - Landing Gear Leg Overhaul - [SN: 190555] 1.00 MTH 2652 2652 Nov23-555.pdf AF-192M - Airframe 192 Month Structural Check - [SN: 190555] 1.00 MTH 610 610 Nov23-555.pdf AF-BC - Airframe Basic Check - [SN: 190555] 263.40 FH 37 9745.8 Nov23-555.pdf AF-96M - Airframe 96 Month Structural Check - [SN: 190555] 1.00 MTH 702 702 Nov23-555.pdf AF-20KC - Airframe 20,000 FC Structural Check - [SN: 190555] 102.00 FC 4.4 448.8 Nov23-555.pdf AP-OH - AP-OH - APU Overhaul - [SN: PWC-ZD0103] 96.60 APUH 25 2415 Nov23-555.pdf EN-LP - EN-LP - Engine LLP Replacement - [SN: P783246] 102.00 FC 162.08 16532.16 Nov23-555.pdf EN-PR - EN-PR - Engine Full Performance Restoration
P783246]- [SN: 263.40 FH 135 35559 Nov23-555.pdf EN-LP - EN-LP - Engine LLP Replacement - [SN: P783255] 102.00 FC 162.08 16532.16 Nov23-555.pdf EN-PR - EN-PR - Engine Full Performance Restoration
P783255]- [SN: 263.40 FH 135 35559 Nov23-555.pdf Query Steps applied:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows1" = Table.SelectRows(Source, each ([Source.Name] = "Dec23-555.pdf" or [Source.Name] = "Nov23-555.pdf")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Description", "Source.Name", "Column2", "Utilisation", "Column4", "Rate", "Amount"}),
SourceColNames = List.Buffer(Table.ColumnNames(#"Reordered Columns")),
StepBack = #"Reordered Columns",
ReplacedValue = Table.ReplaceValue(StepBack, null, null,
(x,y,z)=> if List.Contains({"", "-"}, x) then null else x,
SourceColNames),
AddedIndex = Table.AddIndexColumn(ReplacedValue, "Index", 0, 1, Int64.Type),
Ad_GroupHelper = Table.AddColumn(AddedIndex, "GroupHelper", each if (try Text.Range([Description], 2, 1) otherwise null) = "-" then [Index] else null, type text),
#"Changed Type" = Table.TransformColumnTypes(Ad_GroupHelper,{{"GroupHelper", type number}}),
FilledDown = Table.FillDown(Ad_GroupHelper,{"GroupHelper"}),GroupedRows = Table.Group(FilledDown, {"GroupHelper"}, {{"Description", each Text.Combine([Description], "-"), type text}, {"FirstRow", each Table.FirstN(Table.SelectColumns(Table.FillUp(_, SourceColNames), List.Skip(SourceColNames)), 1) , type table}}),
#"Changed Type1" = Table.TransformColumnTypes(GroupedRows,{{"GroupHelper", type number}})
in
#"Changed Type1"Results:
GroupHelperDescription 0 LG-OH - LG-OH - Landing Gear Leg Overhaul - [SN: 190555] -Correction from period 01-Dec-2023 to 31-Dec-2023 1 AF-192M - Airframe 192 Month Structural Check - [SN: 190555] -Correction from period 01-Dec-2023 to 31-Dec-2023 2 AF-BC - Airframe Basic Check - [SN: 190555] - Correction from
period 01-Dec-2023 to 31-Dec-20233 AF-96M - Airframe 96 Month Structural Check - [SN: 190555] -Correction from period 01-Dec-2023 to 31-Dec-2023 4 AF-20KC - Airframe 20,000 FC Structural Check - [SN: 190555] --Correction from period 01-Dec-2023 to 31-Dec-2023 6 AP-OH - AP-OH - APU Overhaul - [SN: PWC-ZD0103] - Correction
from period 01-Dec-2023 to 31-Dec-2023-- [SN: EN-LP - EN-LP - Engine LLP Replacement
P783246]Correction from period 01-Dec-2023 to 31-Dec-2023-- [SN: EN-PR - EN-PR - Engine Full Performance Restoration
P783246] - Correction from period 01-Dec-2023 to 31-Dec-2023-- [SN: P783255] EN-LP - EN-LP - Engine LLP Replacement
Correction from period 01-Dec-2023 to 31-Dec-2023-- [SN: EN-PR - EN-PR - Engine Full Performance Restoration
P783255] - Correction from period 01-Dec-2023 to 31-Dec-202312 LG-OH - LG-OH - Landing Gear Leg Overhaul 13 AF-192M - Airframe 192 Month Structural Check - [SN: 190555] 14 AF-BC - Airframe Basic Check - [SN: 190555] 15 AF-96M - Airframe 96 Month Structural Check - [SN: 190555] 16 AF-20KC - Airframe 20,000 FC Structural Check - [SN: 190555] 17 AP-OH - AP-OH - APU Overhaul 18 EN-LP - EN-LP - Engine LLP Replacement 19 EN-PR - EN-PR - Engine Full Performance Restoration
P783246]20 EN-LP - EN-LP - Engine LLP Replacement 21 EN-PR - EN-PR - Engine Full Performance Restoration
P783255]