Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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,
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.
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 |
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,
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??
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |