Combine multiple tables from column results

[Jimmi_]

Hi,

Been trying to find a solution if possible for an issue I have been having.

I have a query that is producing the following results:

If I click the table or add as new query then I get the results that I want but only for that individual row:

I am then able to filter out the information that I require however, if I just expand the column I just get errors. 

Is there a way that I can loop through each of the tables individually then combine all the results into one single query/table?

[AlB · Accepted solution]

@Jimmi_ 

If what you show above are the steps you want to apply to each table, sure, you can create a function and then, on the initial table add a custom column with the code

= functionName( [Calendar] )

where Calendar is the name of the column containing the tables to be processed as you show above

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PhilipTreacy]

Hi @Jimmi_ 

So clicking the double headed arrow in the Calendar column header to expand the tables doesn't give you what you want?

Regards

Phil

[Jimmi_]

@AlB @PhilipTreacy .

I think there maybe a limitiation in the data source from D365.

I am trying to extract the data within the calendar rules entity which contains the data that I need. i.e. Start & End times for each person.

If I right click the table text within the column and select add as new query then I am able to get the data required by running the additional steps however if I just expand this column then I get errors returned.

I was therfore wondering if I could loop through all of the tables in the column as though I had clicked on it and combine everything into one large table.

This is the query that I need to run after I have exposed the table:

let
    Source = #"1",
    #"calendarid calendar_calendar_rules1" = Source{0}[calendarid.calendar_calendar_rules],
    #"Removed Other Columns" = Table.SelectColumns(#"calendarid calendar_calendar_rules1",{"calendarid"}),
    #"Expanded calendarid" = Table.ExpandRecordColumn(#"Removed Other Columns", "calendarid", {"ExpandCalendar"}, {"calendarid.ExpandCalendar"}),
    #"Added Custom" = Table.AddColumn(#"Expanded calendarid", "Todays Date", each DateTime.FixedLocalNow()),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Next Week", each Date.AddDays([Todays Date],7)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Todays Date", type datetimezone}, {"Next Week", type datetimezone}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Exp_Cal_Function", each Exp_Cal_Function([Todays Date], [Next Week])),
    #"Expanded Exp_Cal_Function" = Table.ExpandRecordColumn(#"Invoked Custom Function", "Exp_Cal_Function", {"result"}, {"Exp_Cal_Function.result"}),
    #"Expanded Exp_Cal_Function.result" = Table.ExpandTableColumn(#"Expanded Exp_Cal_Function", "Exp_Cal_Function.result", {"Start", "End", "CalendarId"}, {"Exp_Cal_Function.result.Start", "Exp_Cal_Function.result.End", "Exp_Cal_Function.result.CalendarId"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Exp_Cal_Function.result",{"calendarid.ExpandCalendar", "Todays Date", "Next Week"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Exp_Cal_Function.result.CalendarId"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Exp_Cal_Function.result.Start", Order.Ascending}})
in
    #"Sorted Rows"

My thinking was to convert this into a function that I could invoke with a custom column but I cant seem to get the source to accept a parameter which would be from the row number(Index) of the source query.

All this being said I could just be barking up the wrong tree. It works fine when I just look at one table but cannot seem to get it to work with multiples.

Thank you both for taking time to reply to me.

[AlB]

@Jimmi_ 

Well, you'd have to tell what exact type of transformation you want to apply to those tables. Please provide an example along with the explanation. And best if you can provide a pbix with all in it (dummy data if necessary)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Jimmi_

You can 

1. create a custom column where you process the tables row by row and then

2. create a new table with Table.Combine(OutputStep1[CustomColumn])

OutputStep1[CustomColumn] will be a list with all the tables in the newly-created custom column

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Jimmi_]

Hi @AlB ,

Thank you for your prompt reply.

What formula would you suggest I use in my custom column to process the tables row by row. I have tried using table.column to just remove the colums I want but this is just returning an error similar to if I just expand the column.

Thank you in advance for your assistance.