我有一个 powerquery,我想将第 5 行中突出显示的值提取到另一列。我可以轻松使用
Record.Field(#"Added Custom"{4},"Column17")
但我遇到的问题是这个值可以出现在第 12 列到第 20 列之间的任何位置,但仍然出现在第 5 行。我如何在该行中找到它并提取它。谢谢。
我想要一个输出,它将总价格列中的数字与类似的索引号进行比较并返回最低的数字。因此,对于上表,预期输出为:
索引 | 总价 |
------ |-------------|
2 | 40,664.95|
------ |-------------|
9 | 29,706.29 | 29,706.29
------ |-------------|
25 | 25 1,076.36 | 1,076.36
------ |-------------|
32 | 32 4,122.49 | 4,122.49
------ |-------------|
39 | 39 2,759.07 | 2,759.07
美好的一天...我收到了一个非常混乱的数据集需要清理...我的第一个想法是 PQ。下面是实际文件中 10k 多行的片段.txt
,它只是对每个用户(“C:”)重复,以----------
. 首先,该列表是我目前需要提取的信息。第一次导入PQ时是一列;选项卡分开。
"Company-Name." Client No: "5780859"
" " Purchase Order No:
Invoice Date: 30-Sep-23 Unique Invoice No: "123456789"
"12345 Main Street"
"City AB"
"T4A 1B7"
"Account Number 1234567"
"-------------------------------------------------------------------------------"
"REPORT - INDIVIDUAL DETAILS"
--------------
"C:" "1234567890"
"Subscriber Name:" "NAME.NAME SPARE"
"Additional line user name:" ""
"Sublevel:" " "
"Sublevel:" ""
"Reference 1:" ""
"Reference 2:" ""
"Handset Transparency"
"Number/Device Information" ""
"Starting Balance" $0.00
"Last Month's Balance" $0.00
"Current Balance" $0.00
"Monthly Credit" $0.00
"Monthly Balance Adjust" $0.00
"CURRENT CHARGES"
"Monthly Service Plan" $40.00
"Additional Local Airtime" $0.00
"Long Distance Charges" $22.40
"Roaming Charges" $0.00
"Total Taxes:" $7.49
"Total Current Charges:" $69.89
"MONTHLY SERVICE PLAN" 01-Oct-23 to 31-Oct-23
"Service Plan Name" "Total"
"Business SharePro 5GB Q1 offer (01-Oct-23 to 31-Oct-23)" $40.00
"Total Monthly Service Plan Charges" $40.00
"ADDITIONAL LOCAL AIRTIME"
"Service" "Total Airtime" "Free Airtime" "Included Airtime" "Chargeable Airtime" "Total"
"Phone (minutes)" 28:00 0:00 28:00 0:00 $0.00
"Total Additional Local Airtime Charges" $0.00
"LONG DISTANCE CHARGES"
"Service" "Total LD Minutes" "Free LD Minutes" "Included LD Minutes" "Chargeable LD Minutes" "Total"
"Domestic Phone" 28:00 0:00 0:00 28:00 $22.40
"Total Long Distance Charges" $22.40
"ROAMING"
"Service" "Roaming Minutes" "Roaming Charges" "Roaming LD Minutes" "Roaming LD Charges" "Roaming Surcharge" "Total"
"Total Roaming Charges" $0.00
"DO MORE DATA SERVICES"
"Service" "Total Events" "Event Type" "Total"
"Total Do More Data Services Charges" $0.00
"DO MORE VOICE SERVICES"
"Service" "Total Events" "Event Type" "Total"
"Total Do More Voice Services Charges" $0.00
"PAGER SERVICES"
"Service" "Total Messages" "Included Messages" "Chargeable Messages" "Total"
"Total Pager Charges" $0.00
"VALUE-ADDED SERVICES" 01-Oct-23 to 31-Oct-23
"Service" "Total"
"Can - Can/US LD $0.80/min (01-Oct-23 to 31-Oct-23)" $0.00
"Easy Roam INTL - $16/day Business (01-Oct-23 to 31-Oct-23)" $0.00
"Easy Roam US - $14/day Business (01-Oct-23 to 31-Oct-23)" $0.00
"UL Can - Can LD min (01-Oct-23 to 31-Oct-23)" $0.00
"UL domestic SMS / MMS (01-Oct-23 to 31-Oct-23)" $0.00
"Visual Voicemail (01-Oct-23 to 31-Oct-23)" $0.00
"Total Value Added Service Charges" $0.00
"OTHER CHARGES AND CREDIT"
"Charge or Credit" "Total"
"Total Other Charges and Credits" $0.00
"OTHER FEES"
"Service" "Total"
"Other Fees" $0.00
"TAXES"
"" "Total"
"GST" $3.12
"PST - BC" $4.37
"Total Taxes" $7.49
经过大量的尝试后,我使用了以下步骤;
所以,现在我已经按应有的方式显示了行,但这是下一个问题。并非每个用户 (C:) 都具有相同的行数(费用),因此数据会溢出到许多列,而这些列不再位于应有的位置。
有什么办法可以解决吗?这样的东西更适合Python吗?
我有一个零件号列表(如附图所示)。仅当零件号以“WN”开头时,我才想修剪第二个分隔符后的最后 2 个字符。所有其他零件号可以保持其完整的字符串长度。仅供参考,我是使用 Power Query 的新手。
我正在考虑将 TrimEnd 与条件一起使用,但没有幸运地编写代码来执行此操作。我还使用 Microsoft Excel for Mac (Microsoft 365)(如果这有影响的话)
。
我下面有这个 Power Query 代码,它在阅读 PDF 和输出我想要的列方面效果很好。如何修改它以读取包含相同格式的 PDF 文件的文件夹并循环遍历所有 PDF 文件。该文件夹最多可包含 300 个文件。
感谢您的预期帮助
let
Source = Pdf.Tables(File.Contents("C:\Users\MyReport.pdf"), [Implementation="1.3"]),
Page1 = Source{[Id="Page001"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"[image]", type text}, {"TestHeader", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column5", "Column6", "Column8", "Column10"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each not Text.StartsWith([#"TestHeader"], "MyOff")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"[image]", "Column2"}, {"TestHeader", "Column3"}, {"Column7", "Column5"}, {"Column9", "Column6"}}),
Page2 = Source{[Id="Page002"]}[Data],
#"Changed Type2" = Table.TransformColumnTypes(Page2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Column5"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column6", "Column5"}, {"Column7", "Column6"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns2"}),
#"Added Custom" = Table.AddColumn(#"Appended Query", "Test_Date", each Record.Field(#"Appended Query"{0},"Column3")),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each Text.StartsWith([Column1], "NF")),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Column1", "Wells"}, {"Column2", "Wet"}, {"Column3", "Hours"}, {"Column4", "FTHP_p"}, {"Column5", "FTHP_b"}, {"Column6", "Comments"}})
in
#"Renamed Columns1"