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.
Hello
I have earlier posted a question about retrieving the latest data. I have gotten answers that did work, however not the way I need it to. Therefore I want to post the question and the entire use of the data. Hopefully someone can help me.
I am sitting with a bunch of data. dummy:
name date product1 product2 product3
xx 10/10-2016 1 0 1
aw 9/11-2016 1 1 0
xx 20/12-2016 0 0 1
I need both the full query, and then I want to retrieve the latest data from that query. The latest data, I want to have the full functions of a normal query. this meaning I can unpivot a column and enter it in a visual etc. So far I have been able to retrieve the latest data and insert it in a matrix and create a DAX table retrieving the latest data. However none of these methods gives me the ability to unpivot columns. Can anyone help me?
@BiBra,
Add a new blank query and paste the following code to Advanced Editor of the query, then check if you get expected result.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqhQ0lEyNNAHIiMDQzMQxwhEALGBUqxOtFJiOYhrqG8JlzeFyhuC5SEGGAFlYQqMDMCawQbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, date = _t, value = _t, #"product 1" = _t, product2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"date", type date}, {"value", Int64.Type}}), #"Grouped Rows"= Table.Group(Source, {"name"}, {{"most recent", each List.Max([date]), type date}, {"Column", each _, type table}}), #"Expanded Column" = Table.ExpandTableColumn(#"Grouped Rows", "Column", {"date", "product 1", "product2", "value"}, {"Column.date", "Column.product 1", "Column.product2", "Column.value"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Column", each [Column.date] = [most recent]) in #"Filtered Rows"
Regards,
Lydia
'This does not work for me. I do have a question for this though:
Where is it defined the data comes from another table?
@BiBra,
I directly use Enter Data in Power BI Desktop to create a table. You can replace the source code with your own.
If the above Power Query code doesn't work in your scenario, please post expected result based on the sample data you post.
Regards,
Lydia
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |