I'm trying to import data from csv files. Each file contains multiple data sets with a single line dividing different regions. I want to take the regions name and turn it into a data field.
RAW DATA:
1/1/2025 - 1/31/2025
Item,Price Raw,Price Sold,Quantity,Cost,Revenue,Margin,Target,Variance,Condition
Michigan
Apples,1.22,2.12,927,1130.94,1965.24,834.3,1200,765.24,
Banans,0.89,1.98,528,469.92,1045.44,575.52,1258,-212.56,
Pears,1.1,2.08,755,830.5,1570.4,739.9,1202,368.4,
Ohio
Apples,1.22,2.58,583,711.26,1504.14,792.88,1200,304.14,
Banans,0.89,2.01,478,425.42,960.78,535.36,1258,-297.22,
Pears,1.1,2.12,622,684.2,1318.64,634.44,1202,116.64,
DESIRED RESULT:
Date,Region,Item,Price Raw,Price Sold,Quantity,Cost,Revenue,Margin,Target,Variance,Condition
1/31/2025,Michigan,Apples,1.22,2.12,927,1130.94,1965.24,834.3,1200,765.24,
1/31/2025,Michigan,Banans,0.89,1.98,528,469.92,1045.44,575.52,1258,-212.56,
1/31/2025,Michigan,Pears,1.1,2.08,755,830.5,1570.4,739.9,1202,368.4,
1/31/2025,Ohio,Apples,1.22,2.58,583,711.26,1504.14,792.88,1200,304.14,
1/31/2025,Ohio,Banans,0.89,2.01,478,425.42,960.78,535.36,1258,-297.22,
1/31/2025,Ohio,Pears,1.1,2.12,622,684.2,1318.64,634.44,1202,116.64,
I know how to deal with the date, it's getting the Michigan and Ohio translated into a field in each record that I'm not sure about.
Help is appreciated. Thanks.
-Q
When you have split the data, the second column [Price Raw] contains null for all rows where the region name is in the first column [Item]. So, add a custom (or conditional) column, call it Region, with the following formula/logic:
if [Price Raw] = null then [Item] else null
Now, fill down the Region column and filter out rows with null in [Price Raw].
Thanks!
