Power Query Transformation Questions.

[scpournara]

2 transformations I need to complete in Power Query.

1) I am appending multiple .cvs file. The source Name is "AvailableInventory - 08102020.cvs". I can not seem to Add column and extract the "08102020" and create a Date format? Can anyone help?

2) I have thousands of rows of data that express values in both FT and LBS. SAmple data looks like this: (25,645 LB, 10,000 FT, 31,500 LB). Imported the format is ABC 123. I can create new column for FT and LBS, but can not seem to create a new column with the number and change format to a whole number? Help?

 

[sandy666]

I don't know how to do that with cvs file but I think it is possible with csv file ?
any small example of source data would help

 

[scpournara]

Thanks for looking at this. Here is an image.

 

[sandy666]

if you know how to use data from the picture I should congratulate you
anyway, pictures are useless
post a shared examples: csv file and excel file as source data and expected result. Use onedrive, googledrive, dropbox or any similar service

if data is classified - use generic data with the same structure

 

[sandy666]

adapt to you needs

Column1		Column1	Custom
02082020.csv		02082020	02/08/2020

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.TransformColumns(Source, {{"Column1", each Text.BeforeDelimiter(_, "."), type text}}),
    Text = Table.AddColumn(TBD, "Custom", each Text.Insert(Text.Insert([Column1],2,"/"),5,"/")),
    Parse = Table.TransformColumns(Text,{{"Custom", each Date.From(DateTimeZone.From(_)), type date}})
in
    Parse

for the second question I don't know what are you doing there ( no M, no live data so no correct answer ? )

 

[sandy666]

if you want to extract numbers only

Column1		Column1	Custom
28,800 FT		28,800 FT	28,800
-25,464 LB		-25,464 LB	-25,464

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextSel = Table.AddColumn(Source, "Custom", each Text.Select([Column1],{"0".."9",",","-"}))
in
    TextSel

 

[sandy666]

Imho it should be like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    TextSel = Table.AddColumn(Source, "Custom", each Text.Select([Column1],{"0".."9","-"})),
    Type = Table.TransformColumnTypes(TextSel,{{"Custom", type number}})
in
    Type

Column1	Custom
28,800 FT	28800
-25,464 LB	-25464

 

[scpournara]

Thanks for your time. I know better than to send a .jpeg. I am new to Power Query so I did not figure out the formula you set. Here is access to a Folder that has two example files.

fdata - Copy - Google Drive

drive.google.com

 

[sandy666]

check this
you'll need to change path to the csv files

pournara.zip

Compressed (zipped) Folder

1drv.ms

 

[scpournara]

Thank you very much for your help. I was able to redirect the pathname, open the data, and see how you built this query. Awesome!