Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
I am trying to expand multiple binaries (PDF tables) in multiple nested folders so the table content from these PDF files are all usable as data points. I've put in a custom function with a sample file but the problem is that not all of these PDF's have the same format although majority of them do contain the same column names.
Here are the challenges:
I want to put in an M query that has a logic as such:
* If column X exists in the table I read from the PDF file, then rename as XX else pass
In Python this would look something like:
for table in tables:
for i in table.columns:
if i in c.keys():
mylist.append(c[i])
else:
mylist.append(i)
# VARS defined:
table = pd.DataFrame({"Customer Name": [1,2,3,4], "Content": ["s","a","3","4"], "Not exist": ["s","a","3","4"]})
a = ['Content',
'Filename',
'Extension',
'Date accessed',
'Date modified',
'Date created',
'Folder Path',
'Customer Name',
'Assumed Eff. Date']
b = ['Content Temp',
'Filename Temp',
'Extension Temp',
'Date accessed Temp',
'Date modified Temp',
'Date created Temp',
'Folder Path Temp',
'Customer Name Temp',
'Assumed Eff. Date Temp']
c = dict(zip(a,b))
# CODE
for i in df.columns:
if i in c.keys():
mylist.append(c[i])
else:
mylist.append(i)
Is this possible?
Thank you!!
Solved! Go to Solution.
Hi @nimblecat
Here is my solution for transforming column names. You can download the pbix at bottom to see details.
TempNameTable
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PK0nNK1GK1YlWcsvMSc1LzE0Fc1wrgOLFmfl5YJ5zaXFJfm5qkYIfWD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TempName", each [Name] & " Temp")
in
#"Added Custom"
Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSoG4jSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Content = _t, #"Not exist" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", Int64.Type}, {"Content", type text}, {"Not exist", type text}}),
Name1 = TempNameTable[Name],
Name2 = TempNameTable[TempName],
ChangeColumnName = Table.TransformColumnNames(#"Changed Type", each if List.Contains(Name1, _) then let _index = List.PositionOf(Name1, _) in Name2{_index} else _)
in
ChangeColumnName
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @nimblecat
Here is my solution for transforming column names. You can download the pbix at bottom to see details.
TempNameTable
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PK0nNK1GK1YlWcsvMSc1LzE0Fc1wrgOLFmfl5YJ5zaXFJfm5qkYIfWD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TempName", each [Name] & " Temp")
in
#"Added Custom"
Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSoG4jSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Content = _t, #"Not exist" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", Int64.Type}, {"Content", type text}, {"Not exist", type text}}),
Name1 = TempNameTable[Name],
Name2 = TempNameTable[TempName],
ChangeColumnName = Table.TransformColumnNames(#"Changed Type", each if List.Contains(Name1, _) then let _index = List.PositionOf(Name1, _) in Name2{_index} else _)
in
ChangeColumnName
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thank you! Very helpful!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
13 | |
12 |