Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am struggling with a Power Query element.
I have a query linked to a data source (sheet 1 of the Excel workbook "Testbook") that promotes headers, keeps certain columns (about 20), and changes the data type.
The core problem is that the dataset will not always contain all 20 columns (although at least 10 will always exist). For the "Changed Type" part, the code fails when at least 1 column does not exist. For the "Remove Other Columns" part, the "MissingField.Ignore" argument takes care of the issue.
Hence, I am looking for a similar argument on other solution that ignores missing columns.
Later, I'd like to clean up the text in one of the columns (formatted as e.g. "10 01 80-1210") by removing the spaces and dash (end goal would be "1001801210"), but again the query would fail in case of missing columns.
I have enclosed a sample code below (for simplicity, I have used 4 columns instead of 20 columns), but my point is that the code would fail if e.g. the "Contr." column does not exist.)
Thanks a lot in advance, let me know if you need further from me.
let
Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true),
PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Salary", type number}, {"Contr.", type number}})
in
#"Changed Type"
Solved! Go to Solution.
Alright, makes sense,
And just because I am still brand new to M, would I put that in my existing code (below), or where?
let Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true), PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Salary", type number}, {"Contr.", type number}}) in #"Changed Type"
you can use try ... otherwise, e.g. like this
let
Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true),
PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Salary", type number}}),
Custom1 = try Table.TransformColumnTypes(#"Changed Type",{{"Contr.", type number}) otherwise #"Changed Type"
in
Custom1
Thanks Stachu, kinda worked.
Is there, however, any way to tweak the code so that all columns can be variable?
EDIT: The code above does not seem to actually change the number formatting of "Contr."
it works for me here (2 rows, all valid decimals), are you sure the transformation itself doesn't return any errors with full dataset? e.g. some entries in [Contr.] have text?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzUtJRMtYzVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Salary = _t, #"Contr." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Salary", type number}}),
Custom1 = try Table.TransformColumnTypes(#"Changed Type", {{"Contr.", type number}}) otherwise #"Changed Type"
in
Custom1
there is but it's much more complex. You basically need a predefined list of columns and respective types (step ColumnTypes in the code below), and then you need to compare it with actual column names (you can get them using Table.ColumnNames) - not included in the code below
let
TypeTable = #table({"ColumnName", "Type"},{{"Salary", type number},{"Contr.", type number}}),
ColumnTypes = Table.AddColumn(TypeTable, "ColType", each {[ColumnName],[Type]}),
Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true),
PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",ColumnTypes[ColType])
in
#"Changed Type"
After you filter ColumnTypes[ColType] to only include relevant column names, you're good to go
I think this might actually be the way to go, since I know for certain the 20 columns that CAN exist and in case they do, those are the ones that should always be imported (the issue arises when one of them does not exist). This would be a far more flexible solution as well.
Let's say I have the data below in an Excel tabel in the same workbook (let's call i d_columns). How would I adjust your code below accordingly?
Thanks a lot @Stachu
Column NameDate type
Column name | Data type |
ID | Whole number |
Name | Text |
Salary | Decimal number |
Contr. | Decimal number |
Effective date | Date |
Amount | Decimal number |
I don't think you can load it from Excel, I think you need to create in M directly. The reason is, in Excel you cannot store M `type` values, at most you can store a `text` value which has the same name as type does. You can recreate your table from Excel in M, using the code below, you obviously would need to add more entries like {"ID", Int64.Type} to include all
let
TypeTable = #table({"ColumnName", "Type"},{{"ID", Int64.Type},{"Name", type text},{"Salary", type number},{"Contr.", type number}}),
ColumnTypes = Table.AddColumn(TypeTable, "ColType", each {[ColumnName],[Type]})
in
ColumnTypes
Alright, makes sense,
And just because I am still brand new to M, would I put that in my existing code (below), or where?
let Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true), PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Salary", type number}, {"Contr.", type number}}) in #"Changed Type"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.