Hello @Jawed,
I saved the sample file you provided on C:/Data and connected to the file from Power BI.
The result is this file (check the Transform Data tab)
Living Wage Foundation - Power Query.pbix (59.9 KB)
In case the file is not uploading correctly this is the M code inside:
// Raw_Data_Connection
let
Source = Excel.Workbook(
File.Contents("C:\Data\Living Wage Foundation_Profit and Loss Detail.xlsx"),
null,
true
),
#"Original Data_Sheet" = Source{[Item = "Original Data", Kind = "Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Original Data_Sheet", {{"Column1", type text}})
in
#"Changed Type"
// Result
let
Source = Raw_Data_Connection,
#"Removed Top Rows" = Table.Skip(Source, 4),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows", 5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Bottom Rows", [PromoteAllScalars = true]),
#"Filled Down" = Table.FillDown(#"Promoted Headers", {"Column1"}),
#"Trimmed Text" = Table.TransformColumns(#"Filled Down", {{"Column1", Text.Trim, type text}}),
#"Removed Top Rows1" = Table.Skip(#"Trimmed Text", 2),
#"Filtered Rows" = Table.SelectRows(
#"Removed Top Rows1",
each not Text.StartsWith([Column1], "Total")
),
#"Inserted Text Before Delimiter" = Table.AddColumn(
#"Filtered Rows",
"Text Before Delimiter",
each Text.BeforeDelimiter([Column1], " "),
type text
),
#"Added Custom" = Table.AddColumn(
#"Inserted Text Before Delimiter",
"Account Code",
each if Text.Length([Text Before Delimiter]) = 3 then [Column1] else null
),
#"Filled Down1" = Table.FillDown(#"Added Custom", {"Account Code"}),
#"Reordered Columns" = Table.ReorderColumns(
#"Filled Down1",
{
"Account Code",
"Column1",
"Date",
"Transaction Type",
"No.",
"Name",
"Memo/Description",
"Split",
"Amount",
"Balance",
"Text Before Delimiter"
}
),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Date] <> null)),
DetailsDone = Table.RemoveColumns(#"Filtered Rows1", {"Text Before Delimiter"}),
Custom1 = Source,
#"Removed Top Rows2" = Table.Skip(Custom1, 6),
#"Kept First Rows" = Table.FirstN(#"Removed Top Rows2", 1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows", {"Column1"}),
#"Trimmed Text1" = Table.TransformColumns(
#"Removed Other Columns",
{{"Column1", Text.Trim, type text}}
),
Type = Table.RenameColumns(#"Trimmed Text1", {{"Column1", "Type"}}),
#"Merged Queries" = Table.NestedJoin(
Type,
{"Type"},
DetailsDone,
{"Account Code"},
"Details",
JoinKind.FullOuter
),
Expanded = Table.ExpandTableColumn(
#"Merged Queries",
"Details",
{
"Account Code",
"Column1",
"Date",
"Transaction Type",
"No.",
"Name",
"Memo/Description",
"Split",
"Amount",
"Balance"
},
{
"Account Code",
"Column1",
"Date",
"Transaction Type",
"No.",
"Name",
"Memo/Description",
"Split",
"Amount",
"Balance"
}
),
#"Filled Up" = Table.FillUp(Expanded, {"Type"}),
IncomeAddedFinal = Table.SelectRows(#"Filled Up", each ([Account Code] = "461 Accreditation")),
Custom2 = Source,
#"Removed Top Rows3" = Table.Skip(Custom2, 5),
#"Kept First Rows1" = Table.FirstN(#"Removed Top Rows3", 1),
#"Removed Other Columns1" = Table.SelectColumns(#"Kept First Rows1", {"Column1"}),
#"Trimmed Text2" = Table.TransformColumns(
#"Removed Other Columns1",
{{"Column1", Text.Trim, type text}}
),
FinanceStatement = Table.RenameColumns(#"Trimmed Text2", {{"Column1", "Finance Statement"}}),
#"Merged Queries1" = Table.NestedJoin(
FinanceStatement,
{"Finance Statement"},
IncomeAddedFinal,
{"Type"},
"Details",
JoinKind.FullOuter
),
#"Filled Up1" = Table.FillUp(#"Merged Queries1", {"Finance Statement"}),
#"Expanded Details" = Table.ExpandTableColumn(
#"Filled Up1",
"Details",
{
"Type",
"Account Code",
"Column1",
"Date",
"Transaction Type",
"No.",
"Name",
"Memo/Description",
"Split",
"Amount",
"Balance"
},
{
"Type",
"Account Code",
"Column1",
"Date",
"Transaction Type",
"No.",
"Name",
"Memo/Description",
"Split",
"Amount",
"Balance"
}
),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Details", each ([Type] = "Income")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows2", {{"Account Code", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Account Sub-Code"}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}, "en-NZ"),
#"Changed Type1" = Table.TransformColumnTypes(
#"Changed Type with Locale",
{{"Amount", type number}, {"Balance", type number}}
)
in
#"Changed Type1"
If you liked my solution please give it a thumbs up 
.
If I did answer your question please mark my post as a solution 
.
Thank you !
Cristian
