Forum Discussion
Help required to separate the text
I assumed that this behavior is ok. Could you provide correct result of such lines pls?
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-2023 | 260.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-2023 | 90.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-2023 | 99.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-2023 | 260.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-2023 | 99.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-2023 | 260.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
|