Streamline log table

Question

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

v-jianboli-msft · Answer

Hi&nbsp;Syndicate_Admin&nbsp;,
&nbsp;
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&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

syndicate_admin · Answer

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

v-jianboli-msft · Answer

Hi&nbsp;Syndicate_Admin&nbsp;,
&nbsp;
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.
&nbsp;
Best Regards,
Jianbo Li
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

syndicate_admin · Answer

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?

v-jianboli-msft · Answer

Hi&nbsp;Syndicate_Admin&nbsp;,
&nbsp;
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&nbsp;removing sensitive data.
&nbsp;
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
&nbsp;
Best Regards,
Jianbo Li
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Streamline log table

5 Replies

Related content

Microsoft Lakehouse Delta Table Logging

Get Fabric Pipeline activities output into log table

Logging

Notebook Logs

Historical Log support