Power Query - Table in Column

[Rookie_2022]

Hi all,

I would executed an API query for each row of a table. Each row has its API result which gives a table (multiple rows x multiple columns). May I know how to expand this "locations" into rows and columns? Thanks.

import http.client
import json
import pandas as pd

def fetch_maersk_schedule(origin_country, origin_city, dest_country, dest_city, vessel_operator='MAEU', consumer_key='xxxxxxxxxxxxxx'):
    """
    Calls the Maersk Ocean Products API with the given parameters and returns a flattened DataFrame.
    """
    conn = http.client.HTTPSConnection("api.maersk.com")
    headers = {
        'Content-Type': 'application/json',
        'Consumer-Key': consumer_key,
    }
    path = (
        f"/products/ocean-products?"
        f"vesselOperatorCarrierCode={vessel_operator}"
        f"&collectionOriginCountryCode={origin_country}"
        f"&collectionOriginCityName={origin_city}"
        f"&deliveryDestinationCountryCode={dest_country}"
        f"&deliveryDestinationCityName={dest_city}"
    )
    conn.request("GET", path, '', headers)
    res = conn.getresponse()
    data = res.read()

    try:
        json_data = json.loads(data.decode("utf-8"))
        df = pd.json_normalize(
            json_data['oceanProducts'],
            record_path=['transportSchedules', 'transportLegs'],
            meta=[
                'carrierProductId',
                'carrierProductSequenceId',
                'numberOfProductLinks',
                ['transportSchedules', 'departureDateTime'],
                ['transportSchedules', 'arrivalDateTime'],
                ['transportSchedules', 'facilities', 'collectionOrigin', 'cityName'],
                ['transportSchedules', 'facilities', 'collectionOrigin', 'countryCode'],
                ['transportSchedules', 'facilities', 'deliveryDestination', 'cityName'],
                ['transportSchedules', 'facilities', 'deliveryDestination', 'countryCode'],
                ['transportSchedules', 'firstDepartureVessel', 'vesselIMONumber'],
                ['transportSchedules', 'firstDepartureVessel', 'vesselName'],
                ['transportSchedules', 'firstDepartureVessel', 'carrierVesselCode'],
                'vesselOperatorCarrierCode'
            ],
            errors='ignore'
        )
#    except Exception as e:
#        print(f"API call or JSON parsing failed: {e}")
#        df = pd.DataFrame()
#    return df

# Example: call function with specific values
#df = fetch_maersk_schedule('KH', 'Sihanoukville', 'NL', 'Rotterdam')
print(df.head())

# Example: apply to a DataFrame column
# Suppose you have a DataFrame 'locations' with columns: origin_country, origin_city, dest_country, dest_city
# You can do (but beware: this calls the API for every row!):
dataset.apply(
lambda row: fetch_maersk_schedule(row['collectionOriginCountryCode'], row['collectionOriginCityName'], row['deliveryDestinationCountryCode'], row['deliveryDestinationCityName']),
axis=1
)

[v-kpoloju-msft · Accepted solution]

Hi @Rookie_2022,

Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

M Query:

let

    // Simulating API response per row (each row has a nested table in [locations])

    Source = Table.FromRecords({

        [OriginCountry="KH", OriginCity="Sihanoukville", DestCountry="NL", DestCity="Rotterdam",

            locations = #table({"Departure","Arrival","Carrier"},

                        {

                            {#datetime(2025,9,5,8,0,0), #datetime(2025,9,15,10,0,0), "MAEU"},

                            {#datetime(2025,9,6,8,0,0), #datetime(2025,9,16,10,0,0), "MAEU"}

                        })

        ],

        [OriginCountry="US", OriginCity="New York", DestCountry="GB", DestCity="London",

            locations = #table({"Departure","Arrival","Carrier"},

                        {

                            {#datetime(2025,9,7,8,0,0), #datetime(2025,9,17,10,0,0), "MSC"},

                            {#datetime(2025,9,8,8,0,0), #datetime(2025,9,18,10,0,0), "MSC"},

                            {#datetime(2025,9,9,8,0,0), #datetime(2025,9,19,10,0,0), "MSC"}

                        })

        ]

    })

in

    Source

Select the locations column. Click the expand button (two arrows ⇔ at the column header). Choose which fields you want (Departure, Arrival, Carrier). Uncheck Use original column name as prefix (optional). Press OK.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @Rookie_2022,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @Rookie_2022,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @Rookie_2022,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.