Best Of

@Chinmay_Totekar I believe the ticket was closed, with the unsatisfying conclusion that this was best for starting with new dataset, not converting existing ones. I did find two ways around it, though:

• For the first one, make a clone of the dataset using the Schema Management tool (Admin » Governance Toolkit » Schema Management), making sure to have it copy the data. Once done, use the same tool to clear the data out, then temporarily rewrite the ETL to set the output dataset to partitioning and bring in the cloned data and write the data out. It's kind of a pain, but it's what I've been doing for the past couple of years.
• Last month I decided to take the Upsert and Partitioning in Magic ETL self-paced course in Domo University. At around the 25 minute mark in the video it talks about using the CLI tool to partition the dataset for you. I haven't tested it, but it does look like it would work. You'll probably still want to make a clone of the original dataset just in case something goes wrong, but this may be your best bet.

We've noticed that if we change the update method of an existing dataset to Partition from replace, we end up with two records in the dataset for every new one (and both of them look identical, down to the field we're using for partitioning): it was quite the shock to see a dataset I expected to have 91 million rows suddenly had 182 million. The obvious takeaway is that we probably should start from scratch when using partitions, but the benefits of converting existing ETLs is too strong a siren song for me to resist.

Two questions:

• Has anyone else noticed this? Both of us here got bit by this, so I want to know if it's a general bug, if it just affects our instance, or if we're doing it wrong and it's Working As Designed™.
• What would be a way around this? If I have other ETLs downstream of the dataset I don't want to delete the existing one and start from scratch unless I absolutely have to. My quick-and-probably-inefficient idea is to store the data to a secondary dataset (deduping if necessary), set the original to partition, and figure out how to use the even more beta feature to tell it to keep no partitions. Let it run once to clear out the dataset, then reimport the data from the secondary dataset and send it back to the original.

Any help would be appreciated.

You can create an ETL that starts by splitting each comma-separated value into columns (Split Column). That would give you each label/value in it own column but not aligned.

You would have to iterate over all the columns to find the patterns. Label: Value —> Label and Value. With Label: in one column and value in the next column.

Then convert your irregular columns into key-value pairs and unpivot the columns.

If you give us a sample csv file with a few records, we can try to give you a direct example. If you are familiar with running Python in Domo, you can do something like this (below). Again….I don't have a data sample to try it out.

import pandas as pd

import re


# -----------------------------

# 1. Load raw data

# -----------------------------

# If CSV has one column called 'raw':

df = pd.read_csv('raw_events.csv')  # or pd.read_excel('raw_events.xlsx')

# If multiple columns from CSV, combine them into one string per row

df['raw_combined'] = df.apply(lambda row: ','.join(row.dropna().astype(str)), axis=1)


# -----------------------------

# 2. Split rows by comma

# -----------------------------

split_cols = df['raw_combined'].str.split(',', expand=True)


# -----------------------------

# 3. Extract label/value pairs

# -----------------------------

def extract_label_value(cell, next_cell=None):

    if pd.isna(cell):

        return None, None

    # Case 1: Label: Value in same cell

    match = re.match(r'\s*(.+?):\s*(.+)', cell)

    if match:

        return match.group(1).strip(), match.group(2).strip()

    # Case 2: Label: in one cell, value in next

    if next_cell is not None:

        match2 = re.match(r'\s*(.+?):\s*$', cell)

        if match2:

            return match2.group(1).strip(), next_cell.strip()

    return None, None


# Iterate over all columns and extract key/value pairs

records = []

for idx, row in split_cols.iterrows():

    row_data = {}

    i = 0

    while i < len(row):

        key, value = extract_label_value(row[i], row[i+1] if i+1 < len(row) else None)

        if key:

            row_data[key] = value

            # If value was in next cell, skip it

            if re.match(r'.+?:\s*$', row[i]):

                i += 1

        i += 1

    records.append(row_data)


# -----------------------------

# 4. Create flattened DataFrame

# -----------------------------

flat_df = pd.DataFrame(records)


# -----------------------------

# 5. Optional: rename columns to match desired output

# -----------------------------

rename_map = {

    'ID number': 'Session ID and Title',

    'Site': 'Site',

    'Room Layout Comments': 'Room Layout Comments',

    'Start/End': 'Session Start date / Session End date',

    'Schedule Date': 'Schedule date',

    'Sponsor': 'Sponsor',

    'Booked By': 'Booked by',

    'Contact Person': 'Contact person',

    'Registration Start-End Date': 'Registration start / Registration end',

    'Fee': 'Fee',

    'Contract Amount': 'Contract amount',

    'Credits': 'Credits',

    'Comments of Internal': 'Comments of internal',

    'Confirmation Comments': 'Confirmation'

}


flat_df.rename(columns=rename_map, inplace=True)


# -----------------------------

# 6. Save to CSV

# -----------------------------

flat_df.to_csv('flattened_events.csv', index=False)

print("Flattening complete. Saved to flattened_events.csv")