Data transformation

[tangutoori]

Dear All,

can we alble to achevie the below transforamtion ?

Regards,

Narender.

[Anonymous · Accepted solution]

Hi @tangutoori ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query editor, then click "Advanced Editor" to copy and paste the following code, then you can check the steps in the right step bar.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjaINDA0MVCK1cHK1gfTjk7YxCC0GVb1MDkIbYGsxgKZDZE3MsYmBqFNHJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Input", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Input.1", "Input.2", "Input.3", "Input.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Input.1", type text}, {"Input.2", type text}, {"Input.3", type text}, {"Input.4", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Input.1"}, {{"Count", each _, type table [Input.1=nullable text, Input.2=nullable text, Input.3=nullable text, Input.4=nullable text]}, {"min2", each List.Min([Input.2]), type nullable text}, {"min3", each List.Min([Input.3]), type nullable text}, {"min4", each List.Min([Input.4]), type nullable text}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Input.2", "Input.3", "Input.4"}, {"Count.Input.2", "Count.Input.3", "Count.Input.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Count.Input.2", "Count.Input.3", "Count.Input.4"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [min4]<>null then Text.Combine({[Input.1],[min2],[min3],[min4]},"/") else Text.Combine({[Input.1],[min2],[min3]},"/")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Input.1", "min2", "min3", "min4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Output"}})
in
    #"Renamed Columns"

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.