Power BI query editor

[Anonymous]

In excel, the column headers/ name are in date datatype and the number shows the demand. There is a folder containing several such excel files. When I export the folder, and unpivot them, some cells data (demand) are in different datatype, instead of integer some are in DD-MM-YYYY HH:MM:SS datatype.
I have attached the starting advanced editor code and some photos for the reference.

How can I resolve it?

let
    Source = Folder.Files("C:\Users\yd201\Desktop\Learning Power BI\MDS Files update"),
    #"Added Custom2" = Table.AddColumn(Source, "Custom", each Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsb"))),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Added Custom2", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.PromoteHeaders([Transform File], [PromoteAllScalars= true])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.UnpivotOtherColumns([Custom], {"Item", "Description", "Buyer", "Planner", "Location", "Firm/Forecast", "BU"}, "Date", "Demand")),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Added Custom1", "Text Between Delimiters", each Text.BetweenDelimiters([Source.Name], "_", ".", 2, 0), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Between Delimiters","_","/",Replacer.ReplaceText,{"Text Between Delimiters"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Text Between Delimiters", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text Between Delimiters", "MDS Date"}}),
    #"Buffer" = Table.Buffer(#"Renamed Columns"),
    #"Sorted Rows" = Table.Sort(Buffer,{{"MDS Date", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",2),
    #"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Source.Name", "Transform File", "Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Item", "Description", "Buyer", "Planner", "Location", "Firm/Forecast", "BU", "Date", "Demand"}, {"Item", "Description", "Buyer", "Planner", "Location", "Firm/Forecast", "BU", "Date", "Demand"}),

[Anonymous · Accepted solution]

Hi @Anonymous ,

I did my own testing on this but did not encounter the same problem as shown below:

After Unpivot:

Could you please check what is the data type of the Demand column in this and subsequent steps? If you find that its type is not integer, please try to manually change it to integer type.

If your problem still can't be solved, I need you to provide your excel file, you can keep all the column headings but please replace the relevant data in your file with the sample data, thanks!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DaleT · Accepted solution]

Hi,

It's hard to reproduce the issue. But changing the type and making it right in the transform function may solve it. As we can see in the image below, "Transform sample file" is generated automatically. we can change the data type and make it right before later use. 

Dale

[Anonymous · Accepted solution]

Hi @Anonymous ,

I did my own testing on this but did not encounter the same problem as shown below:

After Unpivot:

Could you please check what is the data type of the Demand column in this and subsequent steps? If you find that its type is not integer, please try to manually change it to integer type.

If your problem still can't be solved, I need you to provide your excel file, you can keep all the column headings but please replace the relevant data in your file with the sample data, thanks!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PwerQueryKees]

The Transform File function seems to be the culprit.... It should do the promote, but I see a promote later in the query...

[PwerQueryKees]

Looks like the promote first row to column headers did not work for some of the files.

Either it is done for just 1 file or some of the files start with 1 or more blank rows.

[Anonymous]

Anyone please help.