Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
My last 3 columns 2024, 2025 and 2026 always change from time to time, as it represents year of my sales that I want to track, therefore I created a list (I named it as Years) for these column names since they are dynamic.
However, I am stuck when trying to replace my original M code below with the list (Years) that I created earlier,
= Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", type text}, {"Color", type any}, {"2024", Int64.Type}, {"2025", Int64.Type}, {"2026", Int64.Type}})
I have tried to replace
{"2024", Int64.Type}, {"2025", Int64.Type}, {"2026", Int64.Type}
to
{Year, Int64.Type}
but unfortunately, it does not work. Hope to get some guidance here after hours of searching with no similar answers found, thank you
Regards,
M
Solved! Go to Solution.
If it is only the last three columns that need to have dynamic names, you can set the data types for all the columns by creating a list similar to:
#"Types List" = {{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", Currency.Type}, {"Color", type text}}
& List.Transform(List.LastN(Table.ColumnNames(#"Promoted Headers"),3), each {_, Int64.Type}),
Then you can use it in the Transform.ColumnTypes function like:
#"Promoted Headers" = ...,
#"Types List" = {{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", Currency.Type}, {"Color", type text}}
& List.Transform(List.LastN(Table.ColumnNames(#"Promoted Headers"),3), each {_, Int64.Type}),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", #"Types List")
in
#"Changed Type"
If it is only the last three columns that need to have dynamic names, you can set the data types for all the columns by creating a list similar to:
#"Types List" = {{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", Currency.Type}, {"Color", type text}}
& List.Transform(List.LastN(Table.ColumnNames(#"Promoted Headers"),3), each {_, Int64.Type}),
Then you can use it in the Transform.ColumnTypes function like:
#"Promoted Headers" = ...,
#"Types List" = {{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", Currency.Type}, {"Color", type text}}
& List.Transform(List.LastN(Table.ColumnNames(#"Promoted Headers"),3), each {_, Int64.Type}),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", #"Types List")
in
#"Changed Type"
Hi Ron,
Thank you so much and this is exactly what I was confused about. In fact, I have found a similar solution here
https://goodly.co.in/dynamic-data-types-power-query/
Regards,
M
Hi @M001 ,
Do you mean you have got something like this:
And then want to use something like Transpose:
And after that promote your headers?
Hello! Here is a custom function you can use to detect the column types without specifying column names. Add a new blank query, go to the Advanced Editor and replace the contents with the custom function script below. Name the query fnDetectDataTypes. Then replace your Table.TransformColumnTypes step with this:
= fnDetectDataTypes(#"Promoted Headers")
Custom Function
// ****************************************************************************************************************/
// fnDetectDataTypes
// ****************************************************************************************************************/
// PURPOSE
// - Detect data types from a table's data and transform to the appropriate type
//
// LIMITATIONS
// -
//
// DEVELOPER (Adapted from)
// - https://www.thebiccountant.com/2020/05/01/detect-change-types-of-all-columns-in-power-query/
// ****************************************************************************************************************/
let
fn = (table as table, optional first_n_records as nullable number, optional culture as nullable text) as table =>
let
TextColumns = Table.ColumnsOfType ( table, {type nullable text} ),
TempKey = "--(^_^)--",
ReplaceNulls = Table.ReplaceValue ( table, null, TempKey, Replacer.ReplaceValue, TextColumns),
InvalidTypes = {type list, type record, type table, type function, type type, type null, type duration},
Culture = if culture = null then "en-US" else culture,
TopRows = if first_n_records = null then 200 else top_records, //set default to 200 rows to establish a column type
TopNRows = Table.FirstN(ReplaceNulls, TopRows),
ColumnNameList = Table.ColumnNames(TopNRows),
ColumnDataLists = List.Accumulate(ColumnNameList, {}, (accumulated, i) => accumulated & {Table.Column(TopNRows, i)}),
ColumnTypes = List.Transform(ColumnDataLists, (i) => List.ItemType(i)),
TransformList = List.Select(List.Zip({ColumnNameList, ColumnTypes}), (i) => not List.Contains(List.Transform(InvalidTypes, (j) => Type.Is(i{1}, j)), true)),
TypedTable = Table.TransformColumnTypes(ReplaceNulls, TransformList, Culture),
List.ItemType = (list as list) =>
let
ItemTypes = List.Transform(
list,
each
if Value.Type(Value.FromText(_, Culture)) = type number
then
if Text.Contains(Text.From(_, Culture),"%")
then Percentage.Type
else
if Text.Length(Text.Remove(Text.From(_, Culture), {"0".."9"} & Text.ToList("., -+eE()/'"))) > 0
then Currency.Type
else
if Int64.From(_, Culture) = Value.FromText(_, Culture)
then Int64.Type
else type number
else Value.Type(Value.FromText(_, Culture))
),
ListItemType = Type.Union(ItemTypes)
in
ListItemType
in
let
//RemoveInvalidTypes = Table.RemoveColumns ( TypedTable, Table.ColumnsOfType ( TypedTable, {type list, type record, type table, type function} ) ),
// dataflows currently converts all dates to datetime
PrimitiveTypes = Table.ColumnsOfType(TypedTable, {type nullable number, type nullable text, type nullable logical, type nullable datetime}),
NonConformingTypes = List.RemoveMatchingItems ( Table.ColumnNames ( TypedTable ), PrimitiveTypes ),
NonConformingTypesToText = Table.TransformColumnTypes( TypedTable, List.Zip( { NonConformingTypes,
List.Repeat( {type text}, List.Count( NonConformingTypes ) ) } ) ),
TextColumnsNew = Table.ColumnsOfType (NonConformingTypesToText, {type nullable text} ),
ReplaceTempKey = Table.ReplaceValue(NonConformingTypesToText ,TempKey,"",Replacer.ReplaceValue, TextColumnsNew )
in
ReplaceTempKey
in
fn
Hi Jenn,
My question is more about why the column names for 2024, 2025 and 2026 that I have converted to a List (named as Year) doesnt work when I put in the following code. Thank you
= Table.TransformColumnTypes(#"Promoted Headers",{{Year, Int64.Type}})
If your 'Year' field contains a list of values, like this:
and your objective it to simply set the correct type for the column, it would not be 'Int64.Type' because your field does not contain integers - it contains a list of integers. Therefore, you would leave it as 'type any' by omitting it from the transform column types step.
Hi @M001 ,
Do you mean the column name always changes and is dynamic so you can't quote them in a 'Change Type'?
If so, you could try to split the column change and header promotion. If the structure of the data source stays the same, changing Column 6 through Column 8 to Int64-type, followed by dynamicly promoting the header without mentioning any column name in the first row.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNz8zPU9JRcs4vzSspqgSyPEtSc4FUQFFmcipYIie/CEgbGRiZQChTCGWmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])
in
#"Promoted Headers"
Hi Jasper,
This is a good idea. However, I would like to know how to put the column names that I have converted to list into Table.TransformColumnTypes?
I convert column names for 2024, 2025, 2026 to a list named as Year, and how do I put it into Table.TransformColumnTypes? The code below doesnt work
= Table.TransformColumnTypes(#"Promoted Headers",{{Year, Int64.Type}})
Thank you,
M
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
66 | |
24 | |
18 | |
13 |