No related columns available

[Back2Basics]

I have two tables that have a relationship set up and working. I can select the related field for a table and add fields from the other tables - works perfectly.

However... the data for table_1 is laidout in a way that I can't use. Which is why I've set up the relationship in the first place. I took the data from column_A (table_1) and created that as a seperate table and removed duplicates. then created the relationship. Now I want to add columns to table_2 to get teh data in the right format.

For instance, table_1 has a row per process step. Received/Fully Received/Issued. Then a column for the dates for these.

I want to add a new columns to table_2 titled; Received Date/Fully Received Date/Issue Date - and for these to be populated based on the process type. i'm expecting to use RELATED() to provide this data, but when I type RELATED there are no columns avaiable to pull through. However, if I use RELATEDTABLE table_1 is available - but selecting this produces a syntax error.

I'm not sure why I can't select the columns from the related table. i'm sure I have done this with other projects?

[Greg_Deckler]

Can you provide some mock data for this? I wonder if you could just use Power Query to get the data into the format you want during import.

[Back2Basics]

The data looks like this:

Workflow	Section	Total	ID	Name	Product	Workflow ID	Date
03. Variations	Section
03. Variations	1. Received	1
03. Variations	1. Received		AAA	Name A	A	AAA-001	28/04/2016 13:46:31
03. Variations	2. Fully received	1
03. Variations	2. Fully received		AAA	Name A	A	AAA-001	27/04/2016 23:00:00
03. Variations	3. Determined & Issued	3
03. Variations	3. Determined & Issued		BBB	Name B	B	BBB-001	27/04/2016 09:14:29
03. Variations	3. Determined & Issued		CCC	Name C	C	CCC-001	27/04/2016 09:19:13
03. Variations	3. Determined & Issued		DDD	Name D	D	DDD-001	27/04/2016 09:28:35
05. Notifications	3. Determined & Issued
05. Notifications	1. Received	4
05. Notifications	1. Received		CCC	Name C	C	CCC-002	11/04/2016 07:50:03
05. Notifications	1. Received		BBB	Name B	B	BBB-002	11/04/2016 08:04:46
05. Notifications	1. Received		EEE	Name E	E	EEE-001	21/04/2016 13:40:53

I want it to look like this...

ID	Workflow ID	Date Received	Date Fully Received	Date Issued
AAA	AAA-001	28/04/2016	27/04/2016
BBB	BBB-001	01/04/2016	02/04/2016	27/04/2016
CCC	CCC-001	01/04/2016	02/04/2016	27/04/2016
DDD	DDD-001	01/04/2016	02/04/2016	27/04/2016
EEE	EEE-001	01/04/2016	02/04/2016	21/04/2016
BBB	BBB-002	01/04/2016	02/04/2016	11/04/2016
CCC	CCC-002	01/04/2016	02/04/2016	11/04/2016

[v-sihou-msft · Accepted solution]

@Back2Basics

I can't understand your logic to populate the values in your expected result. I assume you just need to pivot the Section column values into columns.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZK9CsIwFEZfJXRWe5O0td6tbSq4ODi4qEPRCAFboT+Cb29ijCBGqBDCxzeck9xktwuAz8i2alXVq2vTBZNgI49S3eRJxyzL9L6uaklMyGw1BaA6sTSEKGRAE0I5RglyGhwmHuByuFzupB2Lnb+xjCOAXn6skL1sa9XIE9kPACwhq64bnoI8z53AhNxW3wJYII2QLf4WFEXhBCYUtvIL9OJ/C4QQTmCCsJVXwFLksRXEM7K+9uqsjr7X/HlophOl4Qs5xxjG4H4O+ROXIkRjcGVZOpwJpa3cjenHXwOM9UgPDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Workflow = _t, Section = _t, ID = _t, Name = _t, Product = _t, #"Workflow ID" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workflow", type text}, {"Section", type text}, {"ID", type text}, {"Name", type text}, {"Product", type text}, {"Workflow ID", type text}, {"Date", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Workflow", "Name", "Product"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Section]), "Section", "Date", List.Max)
in
    #"Pivoted Column"

Regards,

[Back2Basics]

Actually not worked. I thought it had but the data isn't appearing on the same row.  i've not managed to take a screenshot of the ID column because this is senstive but as you can see, no dates are appearing on the same row as any other...

Any idea of how to fix this? The Workflow ID is unique for each price of work that comes in. The same Id is used for the received, fully received and determined dates - so I would expect it to join up but don't know whats going on??

[Back2Basics]

Many thanks - got it. I replaced null values which will do for now. I might have to find a neater way of sorting that for updating the data but seems to be working for he time being at least.

Many thanks

[Back2Basics]

Hi @v-sihou-msft

This looks like it should work, but when I try it I get an error because there are null values in the data.

there is no easy way for me to remove the null items, is there something I can do?