cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

No related columns available

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?

1 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.

 

66.PNG

 

7.PNG

 

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,

 

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




The data looks like this:

 

WorkflowSectionTotalIDNameProductWorkflow IDDate
03. VariationsSection      
03. Variations1. Received1     
03. Variations1. Received AAAName AAAAA-00128/04/2016 13:46:31
03. Variations2. Fully received1     
03. Variations2. Fully received AAAName AAAAA-00127/04/2016 23:00:00
03. Variations3. Determined & Issued3     
03. Variations3. Determined & Issued BBBName BBBBB-00127/04/2016 09:14:29
03. Variations3. Determined & Issued CCCName CCCCC-00127/04/2016 09:19:13
03. Variations3. Determined & Issued DDDName DDDDD-00127/04/2016 09:28:35
05. Notifications3. Determined & Issued      
05. Notifications1. Received4     
05. Notifications1. Received CCCName CCCCC-00211/04/2016 07:50:03
05. Notifications1. Received BBBName BBBBB-00211/04/2016 08:04:46
05. Notifications1. Received EEEName EEEEE-00121/04/2016 13:40:53

 

I want it to look like this...

 

IDWorkflow IDDate ReceivedDate Fully ReceivedDate Issued
AAAAAA-00128/04/201627/04/2016 
BBBBBB-00101/04/201602/04/201627/04/2016
CCCCCC-00101/04/201602/04/201627/04/2016
DDDDDD-00101/04/201602/04/201627/04/2016
EEEEEE-00101/04/201602/04/201621/04/2016
BBBBBB-00201/04/201602/04/201611/04/2016
CCCCCC-00201/04/201602/04/201611/04/2016

@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.

 

66.PNG

 

7.PNG

 

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,

 

View solution in original post

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??

 

Capture.JPG

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

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?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors