Table Transformation

[jvs5899]

I am working on a board for my quality department and have hit a brick wall. We require parts to be qualified prior to shipment to us from our suppliers. We have a firm date the current version of the part is approved. Per our procedure, they have to requalify the part if there is a 2 year gap in shipments from the last qualification date. The table for the dock gives the part numbers and dates recieved. I have another table that is the last qualification dates. I have included some sample data below, the real tables involve thousand of shipments and parts. I was thinking of doing a transformation where I turn the table so that each part is a row and each column is a reciept date, then do a custom column with the logic for determing the gap between each shipment. However, I don't know how to do it. I have tried the standard transforms but nothing is giving me anything I can use. 

Thanks for you help!

Part #	Qual Date
1G5661231	4/15/2020
1S45897	1/15/2020
7U846540	7/5/2021
2G549681	8/15/2023

Part#	Rec. Date
1S45897	2/25/2025
2G549681	11/7/2024
7U846540	8/16/2024
7U846540	7/2/2024
2G549681	5/11/2024
7U846540	2/11/2024
2G549681	11/13/2023
1G5661231	8/8/2023
7U846540	6/9/2023
1G5661231	4/25/2023
1G5661231	10/27/2022
1G5661231	9/27/2022
7U846540	6/29/2022
7U846540	5/30/2022
7U846540	3/31/2022
1G5661231	3/9/2022
7U846540	2/11/2022
1G5661231	1/25/2022
1G5661231	12/26/2021
1G5661231	10/27/2021
1G5661231	8/8/2021
7U846540	7/15/2021
1G5661231	5/10/2021
1G5661231	3/26/2021
1G5661231	1/7/2021
1G5661231	12/8/2020
1G5661231	9/17/2020
1G5661231	6/14/2020
1S45897	4/8/2020
1G5661231	5/15/2020

[Omid_Motamedise · Accepted solution]

Hi, to put the dates on the columns and determine the part in thefirst column, copy thefollowing formula and past it into your advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7DcAwCEXRXagtGTA/T+ABolQW+68RYqVJe67e2xtoqRnxIGggnbQzMkK2KpdoTC+nn/sdYipYwftxOs5LZVq8R/ENBmQ+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part #" = _t, #"Qual Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part #", type text}, {"Qual Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Qual Date"]), "Qual Date", "Custom", List.Sum)
in
    #"Pivoted Column"

[v-aatheeque]

Hi @jvs5899 ,

If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @jvs5899 ,

we wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

If our response has addressed by @Akash_Varuna @Omid_Motamedise your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-aatheeque]

Hi @jvs5899 ,

we wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

If our response has addressed by @Akash_Varuna @Omid_Motamedise your query, please accept it as a solution so that other community members can find it easily.

[Akash_Varuna · Accepted solution]

Hi @jvs5899 Could you try this please 

let
   
    QualTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7DcAwCEXRXagtGTA/T+ABolQW+68RYqVJe67e2xtoqRnxIGggnbQzMkK2KpdoTC+nn/sdYipYwftxOs5LZVq8R/ENBmQ+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part #" = _t, #"Qual Date" = _t]),
    QualChangedType = Table.TransformColumnTypes(QualTable,{{"Part #", type text}, {"Qual Date", type date}}),

    
    RecTable = Excel.CurrentWorkbook(){[Name="ReceiptTable"]}[Content],
    RecChangedType = Table.TransformColumnTypes(RecTable,{{"Part #", type text}, {"Rec. Date", type date}}),

    
    MergedTable = Table.NestedJoin(RecChangedType, "Part #", QualChangedType, "Part #", "Qualification", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "Qualification", {"Qual Date"}),

    
    FilteredTable = Table.SelectRows(ExpandedTable, each [Rec. Date] >= [Qual Date]),

    
    GroupedTable = Table.Group(FilteredTable, {"Part #"}, {
        {"Data", each Table.Sort(_, {{"Rec. Date", Order.Ascending}})}
    }),

    // Step 6: Add Gap Days Column for Consecutive Shipments
    AddGapColumn = Table.TransformColumns(GroupedTable, {
        {"Data", each Table.AddColumn(_, "Gap Days", (row) =>
            if Table.RowCount(_) = 1 then null
            else let
                CurrentIndex = Table.PositionOf(_, row),
                PreviousDate = if CurrentIndex > 0 then _[Rec. Date]{CurrentIndex - 1} else null
            in if PreviousDate = null then null else Duration.Days(row[Rec. Date] - PreviousDate))
        )}
    }),

    
    FlattenedTable = Table.ExpandTableColumn(AddGapColumn, "Data", {"Part #", "Rec. Date", "Qual Date", "Gap Days"}),
    AddRequalificationFlag = Table.AddColumn(FlattenedTable, "Requires Requalification", each
        if [Gap Days] <> null and [Gap Days] > 730 then "Yes" else "No")
in
    AddRequalificationFlag

[Omid_Motamedise · Accepted solution]

Hi, to put the dates on the columns and determine the part in thefirst column, copy thefollowing formula and past it into your advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7DcAwCEXRXagtGTA/T+ABolQW+68RYqVJe67e2xtoqRnxIGggnbQzMkK2KpdoTC+nn/sdYipYwftxOs5LZVq8R/ENBmQ+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part #" = _t, #"Qual Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part #", type text}, {"Qual Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Qual Date"]), "Qual Date", "Custom", List.Sum)
in
    #"Pivoted Column"