Advocate I

3 years ago

Solved

Unique Value Power Query

Hi,

I am new in Power Query , hope someone can easily fix my issue 🙂

please see the below screen shot which is self explanatory , looking for all records in one row 🙂

Thanks in advance

ajisharavind_99 Paste this code in Advanced Editor and observe:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQyNlHSUQpxDQ4BUo5Ozi5AytDIwABIWZoCCSMDIyNdA0MgAnJAKFaHCF3IGo1I0YWs0Rimy5QsF+LXhcuFBHWhuTAWAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [
                    ID = _t,
                    DESC = _t,
                    ID2 = _t,
                    #"Total Hrs" = _t,
                    #"Hr/Person" = _t,
                    #"Date 1" = _t,
                    #"Date 2" = _t,
                    #"Date 3" = _t
                ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "ID", Int64.Type },
                { "DESC", type text },
                { "ID2", type text },
                { "Total Hrs", Int64.Type },
                { "Hr/Person", Int64.Type },
                { "Date 1", type date },
                { "Date 2", type date },
                { "Date 3", type date }
            }
        ),
    ColumnNames = Table.ColumnNames ( ChangedType ),
    // This step helps in creating the data types for columns before the Expansion
    ColumnsAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "ID", "DESC", "ID2", "Total Hrs", "Hr/Person" },
            {
                {
                    "Transformation",
                    each 
                    Table.FirstN (
                        Table.FillUp ( _, ColumnNames ),
                        1
                    ),
                    ColumnsAndTypes
                }
            }
        ),
    RemovedOtherColumns = Table.SelectColumns ( GroupedRows, { "Transformation" } ),
    ExpandedTransformation = 
        Table.ExpandTableColumn ( 
            RemovedOtherColumns, 
            "Transformation", 
            ColumnNames, 
            ColumnNames 
        )
in
    ExpandedTransformation

AlexisOlson

3 years ago

Select all of your date columns and choose Unpivot Columns > Unpivot Only Selected Columns:

This converts the table to the following where all the empty cells automatically disappear (or can be filtered out at this point):

This may actually be a better shape for your data but if you need it in the previous shape, then you can pivot back to get this all on a single row.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQpxDQ4BUo5Ozi5AytDIwABIWZoCCSMDIyNdA0MgAnJAKFaHCF3IGo1I0YWs0VgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DESC = _t, ID2 = _t, #"Total Hrs" = _t, #"Hr/Person" = _t, #"Date 1" = _t, #"Date 2" = _t, #"Date 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DESC", type text}, {"ID2", type text}, {"Total Hrs", Int64.Type}, {"Hr/Person", Int64.Type}, {"Date 1", type date}, {"Date 2", type date}, {"Date 3", type date}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Date 1", "Date 2", "Date 3"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

4 Replies

daXtreme
Solution Sage
3 years ago
Use GROUBY. Group by all fields that are not your dates and the operation for dates should be... I can't remember 🙂 but you'll figure this out. I'm sure.

AntrikshSharma

Community Champion

3 years ago

ajisharavind_99 Paste this code in Advanced Editor and observe:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQyNlHSUQpxDQ4BUo5Ozi5AytDIwABIWZoCCSMDIyNdA0MgAnJAKFaHCF3IGo1I0YWs0Rimy5QsF+LXhcuFBHWhuTAWAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [
                    ID = _t,
                    DESC = _t,
                    ID2 = _t,
                    #"Total Hrs" = _t,
                    #"Hr/Person" = _t,
                    #"Date 1" = _t,
                    #"Date 2" = _t,
                    #"Date 3" = _t
                ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "ID", Int64.Type },
                { "DESC", type text },
                { "ID2", type text },
                { "Total Hrs", Int64.Type },
                { "Hr/Person", Int64.Type },
                { "Date 1", type date },
                { "Date 2", type date },
                { "Date 3", type date }
            }
        ),
    ColumnNames = Table.ColumnNames ( ChangedType ),
    // This step helps in creating the data types for columns before the Expansion
    ColumnsAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "ID", "DESC", "ID2", "Total Hrs", "Hr/Person" },
            {
                {
                    "Transformation",
                    each 
                    Table.FirstN (
                        Table.FillUp ( _, ColumnNames ),
                        1
                    ),
                    ColumnsAndTypes
                }
            }
        ),
    RemovedOtherColumns = Table.SelectColumns ( GroupedRows, { "Transformation" } ),
    ExpandedTransformation = 
        Table.ExpandTableColumn ( 
            RemovedOtherColumns, 
            "Transformation", 
            ColumnNames, 
            ColumnNames 
        )
in
    ExpandedTransformation

daXtreme

Solution Sage

3 years ago

Here's a version that does not use dynamic text evaluation.

// Transform Table
let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQyNlHSUQpxDQ4BUo5Ozi5AytDIwABIWZoCCSMDIyNdA0MgAnJAKFaHCF3IGo1I0YWs0Rimy5QsF+LXhcuFBHWhuTAWAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [
                    ID = _t,
                    DESC = _t,
                    ID2 = _t,
                    #"Total Hrs" = _t,
                    #"Hr/Person" = _t,
                    #"Date 1" = _t,
                    #"Date 2" = _t,
                    #"Date 3" = _t
                ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            {
                { "ID", Int64.Type },
                { "DESC", type text },
                { "ID2", type text },
                { "Total Hrs", Int64.Type },
                { "Hr/Person", Int64.Type },
                { "Date 1", type date },
                { "Date 2", type date },
                { "Date 3", type date }
            }
        ),
    // Extract column names that start with "Date"
    ExtractColumnNames = List.Select(
        Table.ColumnNames(ChangedType), 
        each Text.StartsWith(_, "Date")
    ),
    // Create a list of lists based on the above
    // to be able to group dynamically, regardless
    // of the number of dates in the original table.
    DynamicArgsForGrouping = List.Transform(
        ExtractColumnNames, 
        each  {
            _,
            (t as table) as nullable date => List.Max(Table.Column(t, _)),
            type nullable date
        }
    ),
    // Do the grouping and enjoy the outcome!
    GroupedTable = Table.Group(
        ChangedType, 
        {"ID", "DESC", "ID2", "Total Hrs", "Hr/Person"},
        DynamicArgsForGrouping
    )
in
    GroupedTable

AlexisOlson

Super User

3 years ago

Select all of your date columns and choose Unpivot Columns > Unpivot Only Selected Columns:

This converts the table to the following where all the empty cells automatically disappear (or can be filtered out at this point):

This may actually be a better shape for your data but if you need it in the previous shape, then you can pivot back to get this all on a single row.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQpxDQ4BUo5Ozi5AytDIwABIWZoCCSMDIyNdA0MgAnJAKFaHCF3IGo1I0YWs0VgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DESC = _t, ID2 = _t, #"Total Hrs" = _t, #"Hr/Person" = _t, #"Date 1" = _t, #"Date 2" = _t, #"Date 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DESC", type text}, {"ID2", type text}, {"Total Hrs", Int64.Type}, {"Hr/Person", Int64.Type}, {"Date 1", type date}, {"Date 2", type date}, {"Date 3", type date}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Date 1", "Date 2", "Date 3"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Forum Discussion

Unique Value Power Query

4 Replies

Related content

Power Query help to extract unique values

Unique value

unique values filter in power bi dashboard

Count unique values

Unique values