Forum Discussion
Randomly arranged values from PDF page
Hi,
im getting data from PDF as below
= Table.AddColumn(#"Removed Other Columns", "Custom", each Pdf.Tables([Content]))
and due to values are from Page not Table format, it's randomly ordered as below.
How can I reorder same type of values under same column?
FYI,
let
// Get list of files in SharePoint folder
Source = SharePoint.Contents("xxxxxxxxx.com"),
// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"Rollout" = Documents{[Name="Rollout"]}[Content],
// Get list of files in SharePoint folder
#"Work Order PDF" = #"Rollout"{[Name="Work Order PDF"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"Work Order PDF",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Pdf.Tables([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Page001")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Sorted Rows",{"Name", "Custom.Name", "Custom.Data"}),
#"Expanded Custom.Data1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6", "Custom.Data.Column7", "Custom.Data.Column8", "Custom.Data.Column9", "Custom.Data.Column10", "Custom.Data.Column11", "Custom.Data.Column12", "Custom.Data.Column13", "Custom.Data.Column14", "Custom.Data.Column15", "Custom.Data.Column16", "Custom.Data.Column17"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom.Data1", each ([Custom.Data.Column1] = "T-安裝工程" or [Custom.Data.Column1] = "T-拆除工程(專案)" or [Custom.Data.Column1] = "T-遷移工程")),
// Dynamically get column names and expand
#"Listed Columns" = Table.ColumnNames(#"Filtered Rows1"[Custom.Data]{0}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Custom.Data", #"Listed Columns")
in
#"Expanded Custom.Data"
6 Replies
Hi Anonymous ,
1. What about just clicking here to expand?
This will help you automatically generate a series of conversion steps.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi Anonymous,
Thank you for reaching out to the Microsoft Fabric Forum Community.
one approach you can take is to first unpivot the extracted columns (e.g., Custom.Data.Column1 to Column17) so all values are stacked under a single column with a corresponding attribute for their original position. This allows you to treat all the scattered values uniformly. Once unpivoted, you can apply logic to categorize or identify what each value represents—whether it's a numeric amount, a label, or something like "X 1" that might act as an indicator. After categorizing the values, you can regroup them using an index or a row context and then pivot them back into a structured table, with each category forming a distinct column.
This process won't be automatic and might require some trial-and-error depending on how consistent the layout is across different PDF files, but it gives you a structured way to transform disorganized PDF page data into a usable tabular format. While clicking the expand icon (as suggested) is a good starting point, it doesn't resolve the core issue in this case, since the problem lies in how the data is interpreted and aligned—not in how it is expanded.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
