TransformColumnTypeCustom

0

Hi Teacher,

I would like to know if this programming is your own or is this part of the code of the web page?

Coronavirus Excel Stats Update - Pull in New Columns Automatically

https://www.youtube.com/watch?v=vXoKKLxbzVE&t=2s

-----------------------------------------------------------------------------------------------------------------

= (table as table) as table =>

let

    Top200Rows = Table.FirstN(table, 200), //we use up to 200 rows to establish a column type

    ColumnNameList = Table.ColumnNames(Top200Rows),

    ColumnDataLists = List.Accumulate(ColumnNameList, {}, (accumulated, i) => accumulated & {Table.Column(Top200Rows, i)}),

    ColumnTypes = List.Transform(ColumnDataLists, (i) => List.ItemType(i)),

    TransformList = List.Zip({ColumnNameList, ColumnTypes}),

    TypedTable = Table.TransformColumnTypes(table, TransformList),

        List.ItemType = (list as list) =>

        let            

            ItemTypes = List.Transform(

                            list, 

                            each if Value.Type(Value.FromText(_)) = type null then type any 

                                 else if Value.Type(Value.FromText(_)) = type number then

                                         if Number.IntegerDivide(Value.FromText(_), 1) = Value.FromText(_) then Int64.Type else type number

                                 else Value.Type(Value.FromText(_))

                        ),

            ListItemType = Type.Union(ItemTypes)

        in

            ListItemType

in

    TypedTable

--------------------------------------------------------------------------------------------------------

Kind Regards,

Katherine.

Answer
Discuss

Discussion

I would like to know this Code where is it from :-)
Kathuna (rep: 2) May 12, '20 at 5:08 am
Add to Discussion

Answers

0
Selected Answer

I'm pretty sure that is the code from the Power Query Editor and most of that is auto-generated when you make a new query like I show in the original corona virus stats video tutorial - in the video that you linked, I edited the power query to make it work better for this situation.

Discuss

Discussion

Hello, But it wasn't automaticly I copy-page from your Example sheet :-( For this reazon I was very confused in it

Katherine.
Kathuna (rep: 2) May 13, '20 at 5:00 am
Can you be more specific? Because I only found that code through the Power Query Editor ...
don (rep: 1900) May 13, '20 at 2:14 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login