Power Query Code Modification to shift cells to the left

[mazher]

Hi Everyone,
I am using the below code but at the end there is some data that nee do be shifted to left

let
    SourceFile = "C:\Users\ABC\Downloads\111736.PDF",
    TableList = Pdf.Tables(File.Contents(SourceFile), [Implementation="1.3"]),
    #"Filtered Rows" = Table.SelectRows(TableList, each ([Kind] = "Table")),
    MergedTables = Table.Combine(#"Filtered Rows"[Data]),
    BeforeFilter = MergedTables,
    FilteredMergedTables = Table.SelectRows(MergedTables, each Text.Length([Column1]) = 5 or Text.Length([Column2]) = 5 )
in
    FilteredMergedTables

I have attached the screenshot of how its looks at the end

Any help will be much appreciated as I need to shift data as shown in the image to one cell left ( where length of data in Column2 is equal to 5),

Thanks in advance

 

[alansidman]

Would be helpful if you also showed what your source data looks like before you before you applied any Mcode. Since you don't show any column names, you have made this a guessing game. Help us to help you with full details.

 

[smozgur]

I think Merging & Trimming & Splitting is one of the possible ways:

In this method, we merge all columns in a single column by using the "space" as the delimiter. Then we use the Table.TransformColumns() function to trim the merged text value, so we simply remove the spaces on both ends (nulls from the first and the last columns). Then we reverse the merge by using the Table.SplitColumn() function. Note that we remove the last column name to drop it and keep the useful ones only.

Important note: If the data also contains spaces, then this method won't work correctly, obviously.

(Place your code's let body in all sample codes below. I started by using FilteredMergedTable directly to save some space)

l
let
    // ...
    // Place your code here that provides the FilteredMergedTable identifier
    // ...
    MergeColumns = Table.TransformColumns(
                    Table.CombineColumns(
                        Table.TransformColumnTypes(FilteredMergedTables, List.Transform(Table.ColumnNames(FilteredMergedTables), each {_, type text })), 
                        Table.ColumnNames(FilteredMergedTables), Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Merged"
                    ), 
                    {"Merged", each Text.Trim(_)}
                    ),
    SplitColumn = Table.SplitColumn(MergeColumns, 
                    "Merged", 
                    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
                    List.RemoveLastN(Table.ColumnNames(FilteredMergedTables)))
in
    SplitColumn

Another way could be Shifting Columns if the Column1 value is null. This is not fancy like the first or next methods since it requires changing the column names in the code.

let
    // ...
    // Place your code here that provides the FilteredMergedTable identifier
    // ...
    ShiftNulls = Table.FromRecords(Table.TransformRows(FilteredMergedTable,
        (r) => Record.TransformFields(r, 
                {
                    {"Column1", each if r[Column1] is null then r[Column2] else _},
                    {"Column2", each if r[Column1] is null then r[Column3] else _},
                    {"Column3", each if r[Column1] is null then r[Column4] else _}
                }
        )
    )),
    Result = Table.RemoveColumns(ShiftNulls, List.Last(Table.ColumnNames(FilteredMergedTable))) 
in
    Result

And finally, the Shifting Columns method again, but this time it works dynamically. (This is the fancy one)

let
    // ...
    // Place your code here that provides the FilteredMergedTable identifier
    // ...
    ColumnNames = Table.ColumnNames(FilteredMergedTable),
    ShiftNulls = Table.FromRecords(Table.TransformRows(FilteredMergedTable,
        (r) => Record.TransformFields(r, 
            List.Accumulate(
                {0..List.Count(ColumnNames) - 2},
                {},
                (state, current) => state & {{ColumnNames{current}, each if Record.Field(r, ColumnNames{0}) is null then Record.Field(r, ColumnNames{current + 1}) else _}}
            )
        )
    )),
    Result = Table.RemoveColumns(ShiftNulls, List.Last(ColumnNames)) 
in
    Result

All methods remove the last column at the end.

 

[GraH]

Hi,

Looking at the code and the result you get, I think you'd better attack the issue in the tables before this step where you combine each table. The shift can be the result of column headers that do not align.

MergedTables = Table.Combine(#"Filtered Rows"[Data]),

But before seeing the actual content of each table it's difficult to provide any good code to solve the issue.
Maybe this can do the trick.

    CleanTables = List.Transform(#"Filtered Rows"[Content], each 
                    Table.Transpose(
                        Table.SelectRows(Table.Transpose(_), 
                            each not List.IsEmpty(
                                List.RemoveMatchingItems(Record.FieldValues(_), {"", null})
                            )
                        ) 
                    )
                ),
    Combine_tables = Table.Combine(CleanTables)

in
    Combine_tables

 

[mazher]

Would be helpful if you also showed what your source data looks like before you before you applied any Mcode. Since you don't show any column names, you have made this a guessing game. Help us to help you with full details.

I think Merging & Trimming & Splitting is one of the possible ways:

In this method, we merge all columns in a single column by using the "space" as the delimiter. Then we use the Table.TransformColumns() function to trim the merged text value, so we simply remove the spaces on both ends (nulls from the first and the last columns). Then we reverse the merge by using the Table.SplitColumn() function. Note that we remove the last column name to drop it and keep the useful ones only.

Important note: If the data also contains spaces, then this method won't work correctly, obviously.

(Place your code's let body in all sample codes below. I started by using FilteredMergedTable directly to save some space)

l
let
    // ...
    // Place your code here that provides the FilteredMergedTable identifier
    // ...
    MergeColumns = Table.TransformColumns(
                    Table.CombineColumns(
                        Table.TransformColumnTypes(FilteredMergedTables, List.Transform(Table.ColumnNames(FilteredMergedTables), each {_, type text })),
                        Table.ColumnNames(FilteredMergedTables), Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Merged"
                    ),
                    {"Merged", each Text.Trim(_)}
                    ),
    SplitColumn = Table.SplitColumn(MergeColumns,
                    "Merged",
                    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
                    List.RemoveLastN(Table.ColumnNames(FilteredMergedTables)))
in
    SplitColumn

Another way could be Shifting Columns if the Column1 value is null. This is not fancy like the first or next methods since it requires changing the column names in the code.

let
    // ...
    // Place your code here that provides the FilteredMergedTable identifier
    // ...
    ShiftNulls = Table.FromRecords(Table.TransformRows(FilteredMergedTable,
        (r) => Record.TransformFields(r,
                {
                    {"Column1", each if r[Column1] is null then r[Column2] else _},
                    {"Column2", each if r[Column1] is null then r[Column3] else _},
                    {"Column3", each if r[Column1] is null then r[Column4] else _}
                }
        )
    )),
    Result = Table.RemoveColumns(ShiftNulls, List.Last(Table.ColumnNames(FilteredMergedTable)))
in
    Result

And finally, the Shifting Columns method again, but this time it works dynamically. (This is the fancy one)

let
    // ...
    // Place your code here that provides the FilteredMergedTable identifier
    // ...
    ColumnNames = Table.ColumnNames(FilteredMergedTable),
    ShiftNulls = Table.FromRecords(Table.TransformRows(FilteredMergedTable,
        (r) => Record.TransformFields(r,
            List.Accumulate(
                {0..List.Count(ColumnNames) - 2},
                {},
                (state, current) => state & {{ColumnNames{current}, each if Record.Field(r, ColumnNames{0}) is null then Record.Field(r, ColumnNames{current + 1}) else _}}
            )
        )
    )),
    Result = Table.RemoveColumns(ShiftNulls, List.Last(ColumnNames))
in
    Result

All methods remove the last column at the end.

Hi,

Looking at the code and the result you get, I think you'd better attack the issue in the tables before this step where you combine each table. The shift can be the result of column headers that do not align.

MergedTables = Table.Combine(#"Filtered Rows"[Data]),

But before seeing the actual content of each table it's difficult to provide any good code to solve the issue.
Maybe this can do the trick.

    CleanTables = List.Transform(#"Filtered Rows"[Content], each
                    Table.Transpose(
                        Table.SelectRows(Table.Transpose(_),
                            each not List.IsEmpty(
                                List.RemoveMatchingItems(Record.FieldValues(_), {"", null})
                            )
                        )
                    )
                ),
    Combine_tables = Table.Combine(CleanTables)

in
    Combine_tables

Thanks Everyone

let
    // Define the source file path
    SourceFile = "C:\Users\ABC\Downloads\111736.PDF",

    // Extract tables from the PDF
    ExtractedTables = Pdf.Tables(File.Contents(SourceFile)),

    // Filter out non-table elements
    FilteredTables = Table.SelectRows(ExtractedTables, each ([Kind] = "Table")),

    // Merge all filtered tables into a single table
    MergedTables = Table.Combine(FilteredTables[Data]),

    // Preserve the original merged tables for later reference
    OriginalMergedTables = MergedTables,

    // Filter rows with a specific condition
    FilteredMergedTables = Table.SelectRows(MergedTables, each Text.Length([Column2]) = 10),

    // Create a new table with modified columns
    ModifiedTable = Table.FromRecords(
        Table.TransformRows(FilteredMergedTables,
            (row) =>
                [Column1 = if Text.Length(row[Column2]) = 10 then row[Column2] else row[Column1],
                 Column2 = if Text.Length(row[Column2]) = 10 then row[Column3] else row[Column2],
                 Column3 = if Text.Length(row[Column2]) = 10 then row[Column4] else row[Column3]]
        )
    ),

    // Remove original rows that were modified
    OriginalRowsRemoved = Table.SelectRows(OriginalMergedTables, each Text.Length([Column2]) <> 10),

    // Combine modified and original tables
    CombinedTables = Table.Combine({OriginalRowsRemoved, ModifiedTable}),

    // Change column type
    ConvertedColumnType = Table.TransformColumnTypes(CombinedTables, {{"Column1", Int64.Type}}),

    // Remove rows with errors
    RowsWithErrorsRemoved = Table.RemoveRowsWithErrors(ConvertedColumnType, {"Column1"}),

    // Filter out rows with null values in Column1
    NonNullColumn1Rows = Table.SelectRows(RowsWithErrorsRemoved, each [Column1] <> null),

    // Change column type to currency
    CurrencyColumnType = Table.TransformColumnTypes(NonNullColumn1Rows, {{"Column3", Currency.Type}}),

    // Select desired columns
    SelectedColumns = Table.SelectColumns(CurrencyColumnType, {"Column2", "Column3"}),

    // Duplicate a column
    DuplicatedColumn = Table.DuplicateColumn(SelectedColumns, "Column2", "Column2 - Copy"),

    // Split a column by delimiter
    SplitColumn = Table.SplitColumn(DuplicatedColumn, "Column2 - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column2 - Copy.1", "Column2 - Copy.2", "Column2 - Copy.3"}),

    // Change column types
    TransformedColumnTypes = Table.TransformColumnTypes(SplitColumn, {{"Column2 - Copy.1", type text}, {"Column2 - Copy.2", type date}, {"Column2 - Copy.3", Int64.Type}}),

    // Sort rows based on date and number
    SortedRows = Table.Sort(TransformedColumnTypes, {{"Column2 - Copy.2", Order.Ascending}, {"Column2 - Copy.3", Order.Ascending}}),

    // Remove unnecessary columns
    ColumnsRemoved = Table.RemoveColumns(SortedRows, {"Column2 - Copy.1", "Column2 - Copy.2", "Column2 - Copy.3"}),

    // Rename columns
    RenamedColumns = Table.RenameColumns(ColumnsRemoved, {{"Column2", "Invoice No"}, {"Column3", "Amount"}})
in
    RenamedColumns

I have modified my code and it working presently, until some complex situation arises.
Now please can someone check the above code and also please modify it to make the selection of the pdf file dynamic.

Thanks in advance