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.
Good morning
I have a data record in a SQL database, which is saved as follows:
Date | Type | Value |
23/11/2022 14:26 | Registration | 3984XXX |
23/11/2022 14:26 | Loading area | A |
23/11/2022 14:26 | Loaded weight | 5210 |
23/11/2022 14:27 | Loaded volume | 7500 |
23/11/2022 14:27 | Registration | 2565YYY |
23/11/2022 14:27 | Loading area | B |
23/11/2022 14:27 | Loaded weight | 3680 |
23/11/2022 14:27 | Loaded volume | 4500 |
23/11/2022 14:27 | Registration | 3456KLN |
23/11/2022 14:27 | Loading area | C |
23/11/2022 14:29 | Loaded weight | 6510 |
23/11/2022 14:29 | Loaded volume | 8875 |
I try to dynamize the central column, to present the data in the desired way, and I get this:
Date | Registration | Loading area | Loaded weight | Loaded volume |
23/11/2022 14:26 | 3984XXX | null | null | null |
23/11/2022 14:26 | null | A | null | null |
23/11/2022 14:26 | null | null | 5210 | null |
23/11/2022 14:27 | null | null | null | 7500 |
23/11/2022 14:27 | 2565YYY | null | null | null |
23/11/2022 14:27 | null | B | null | null |
23/11/2022 14:27 | null | null | 3680 | null |
23/11/2022 14:27 | null | null | null | 4500 |
23/11/2022 14:27 | 3456KLN | null | null | null |
23/11/2022 14:27 | null | C | null | null |
23/11/2022 14:29 | null | null | 6510 | null |
23/11/2022 14:29 | null | null | null | 8875 |
And I need the table like this:
Registration | Loading area | Loaded weight | Loaded volume | |
23/11/2022 | 3984XXX | A | 5210 | 7500 |
23/11/2022 | 2565YYY | B | 3680 | 4500 |
23/11/2022 | 3456KLN | C | 6510 | 8875 |
I know that grouping the date by days, on other occasions, is solved, but in such a case they differ just minutes, I do not achieve a solution.
Could you help me?
Thanks a lot
Hi @Syndicate_Admin ,
What do they look like? Could you please give a simple example about this?
Can you please share more details to help us clarify your scenario?
Please provide me with more details about your table or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Syndicate_Admin ,
Do they have some kind of logic? Or are they random? If they are random, please consider adjusting your data, otherwise it will be almost impossible to convert them into the results you want.
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Each data is with its corresponding Type. Is there a way to group the data as a subset that is then transformed into a row to get the final result?
Let me explain: each transport can be characterized by these 4 parameters. Identify them as load 1, load 2, load 3, and then transform the data.
It's possible?
Hi @Syndicate_Admin ,
Please try:
Change the day to date type - create an index column for every four rows -pivot your data
Here is the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBNC4IwHMfxtyI7C7r/HpzdrG5Jh4hQxMPAYQPTsFn07puXipjmafzYl/FhRYG20ijko+PzOh4n2QwKlX6BgAQYBxACeJiugNvLg6r1zfTS6K61k8SCZlk2VaedrHRbe7JX0s5krlOV91C6Phu7GeDQ1Uaf9t41w2XkRiycbH+0wDjL83zu5S/t+o/grSVcLNbS5VpCGd+l+4XajauLHVrO3H8bO7RCRAyV5Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"Type", type text}, {"Value", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date", type datetime}}, "en-GB"),
#"Added Custom1" = Table.AddIndexColumn(#"Changed Type with Locale","Index",1,1),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each Number.RoundUp([Index]/4)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Type", "Date", "Value"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/4)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
#"Removed Columns1"
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for your very detailed answer.
I have an extra problem: not all records are correct, there are incomplete orders that do not have the 4 data, and there are some that are not in that order.
Would you have a solution?
Thanks a lot
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |