Merging Tables

(0) Share

Report

Posted on by HansSMT

Hello.
Hope someone can help.

I have, simplified, the following problem.

In a sharepoint list there are records for each day for up to 2 trips according to the following scheme.

Schema:
(trip1/2 of course include addresses etc))
01.07.2021; Trip1
01.07.2021; Trip2
02.07.2021; Trip1
03.07.2021; Trip1
03.07.2021; Trip2
...

For each day, these trips should now be summarized in a daily rate in a catalog, thus:
01.07.2021; Trip1; Trip2
02.07.2021; Trip1
03.07.2021; Trip1; Trip2
...

I have now selected this data in 2 tables: col_trip1, col_trip2 with the columns: Date, Trip.

In the Items property of a catalog I need to merge these data accordingly, but haven't found a correct code yet, as:

AddColumns(col_trip1;
"Trip2"; LookUp(col_trip2;
col_trip1.Date = col_trip2.Date;
Trip))

Problem: Defining the (same-named) columns in the two tables correctly.

Thank you very much for your help.

Hans

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@HansSMT

You could use GroupBy() on your list for the date and use a gallery and subgallery to display your data. Shane Young has a video to show you how to do it. https://www.youtube.com/watch?v=MDhIjM08u-w

Was this reply helpful? Yes No
HansSMT 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Drrickryp,
thanks for your answer.
But can you also give me a solution for my problem approach?

Thanks. Hans

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@HansSMT

The typical gallery/subgallery solutions using GroupBy is shown in the screenshot. It demonstrates how a table with multiple columns can be consolidated by the date of the order. In your case, the trip data and details would be in the "Restofdata" that could be displayed in a subgallery inside the outer gallery that shows the unique dates. The Items property of the outer gallery is shown on the left and the items property of the inner gallery is ThisItem.RestofData. The individual columns in the inner gallery for the date in the outer gallery are in labels like ThisItem.Country, ThisItem.ItemType and ThisItem.ShipDate. Additional columns can be added as desired from the rest of the data in the inner gallery.

Was this reply helpful? Yes No
HansSMT 11 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
I had understood the groupby solution. But I asked about the error in my previous statement:
AddColumns(col_trip1;
"Trip2"; LookUp(col_trip2;
col_trip1.Date = col_trip2.Date;
Trip))
What's the correct coding?

Another problem arises in the GroupBy function with a column from a DropDown control. The German code is:
GroupBy(col_Tour; "Date"; "Name"; "DATA")
"Name" is a DropDown-Column. And the error is: Column "Name" unavailable.

Thanks for your help. Hans

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@HansSMT

Certain words are reserved in PowerApps and SharePoint. See #5 https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-PowerApps/ba-p/848842

Also if you change the name of a column in SharePoint, the original name has to be used, see #6 . To find the original name, you have to go back into SharePoint to look for it. https://social.msdn.microsoft.com/Forums/office/en-US/75ca6fab-56f3-4bf4-aae0-2d29821778a2/how-to-get-internal-names-of-columns-in-sharepoint-lists?forum=sharepointdevelopmentlegacy

With regards to what you did wrong in your original formula, with all due respect, i don't have the patience to figure it out when a better solution seems available.

Was this reply helpful? Yes No
HansSMT 11 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
I understand. That's Ok.
(I'm a newbie. I just wanted to know a little about joining two tables. 😉)
Thanks, Hans.

Was this reply helpful? Yes No