2 ACCEPTED SOLUTIONS
@burakkaragoz and @Elena_Kalina
The solution you provided wont work because each source has different date columns.
If you make the top row a header or unpivot the whole table as you have kindly suggested then it produces the wrong answer.
Try it and share a PBIX if you think it works. Thanks
Hi @powerbricco
Sorry, please, I did not immediately pay attention to your specific table, since in each subsequent line with the dates there was a shift for one day, while in your final version you would like to see only the first mention.
In accordance with this, I edited all the steps that you must perform:
// Step 1: Use first row as headers
= Table.PromoteHeaders(#"PreviousStep", [PromoteAllScalars=true])
// Step 2: Remove Source.Name column
= Table.RemoveColumns(#"Promoted Headers",{"Source.Name"})
// Step 3: Filter out banana and pear from ART column
= Table.SelectRows(#"Removed Columns", each ([ART] <> "banana" and [ART] <> "pear"))
// Step 4: Unpivot other columns (keeping ART as identifier)
= Table.UnpivotOtherColumns(#"Filtered Rows", {"ART"}, "Attribute", "Value")
// Step 5: Add index column starting from 1
= Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type)
// Step 6: Filter to keep only needed rows (≤15 and specific indexes)
= Table.SelectRows(
#"Added Index",
each Number.From([Index]) <= 15 or
List.Contains({31, 61, 91, 121, 151}, Number.From([Index]))
)
// Step 7: Replace dates for specific indexes
= Table.ReplaceValue(
#"Filtered Index Rows",
each [Date],
each if [Index] = 31 then #date(2025, 1, 16)
else if [Index] = 61 then #date(2025, 1, 17)
else if [Index] = 91 then #date(2025, 1, 18)
else if [Index] = 121 then #date(2025, 1, 19)
else if [Index] = 151 then #date(2025, 1, 20)
else [Date],
Replacer.ReplaceValue,
{"Date"}
)// Step 8: Rename 'Attitude' column to 'Date'
= Table.RenameColumns(#"PreviousStep", {{"Attitude", "Date"}})
// Step 9: Convert text dates to proper date format
= Table.TransformColumns(
#"Renamed Columns",
{
{"Date", each
try Date.FromText(_, "en-GB") // Try day/month/year format first
otherwise
try Date.FromText(_, "en-US") // Then try month/day/year
otherwise null, // Return null if conversion fails
type date}
}
)
// Step 10: Correct specific dates based on Index
= Table.ReplaceValue(
#"Converted Date Format",
each [Date],
each if [Index] = 31 then #date(2025, 1, 16)
else if [Index] = 61 then #date(2025, 1, 17)
else if [Index] = 91 then #date(2025, 1, 18)
else if [Index] = 121 then #date(2025, 1, 19)
else if [Index] = 151 then #date(2025, 1, 20)
else [Date],
Replacer.ReplaceValue,
{"Date"}
)
// Step 11: Sort by Date column
= Table.Sort(#"Corrected Specific Dates", {{"Date", Order.Ascending}})
// Step 12: Remove Index column
= Table.RemoveColumns(#"Sorted Rows", {"Index"})
Here is a screenshot with my result if you suddenly do not understand something, write to explain
@burakkaragoz and @Elena_Kalina
The solution you provided wont work because each source has different date columns.
If you make the top row a header or unpivot the whole table as you have kindly suggested then it produces the wrong answer.
Try it and share a PBIX if you think it works. Thanks
Hi @powerbricco ,
You can refer to steps as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZY7bsMwDIbv4jloSIqU5LEHaIemnYoMaZGxDwTo/SvSkczFmzQYBmhRovyT5gfZ7+8TTofp9PZUbMBYLM86kwGKTWxWsq5SUsuiM5E1JmmkiMZInnWvhGJRNEZAYzjKdD4sKr/Xy63cKOsGWmJsmC1S1CJTsZYCc7IH6JhmSy3p47MNNYxgWaMm8XH5LpfmaUFRbTARlmVsIlksA7aiTQWhzaC9hhCTjYPtsrKKuuqcfv5un9eH58vXtcw+vrzqWjwCHgmsKhTvsHeCd8g76B1wDszeyd5J3vEZgM8AfAbgM4A1Ay2MGgkRZQgPVeVOAiN356FKNBIWSLrzsEmCb8oesdDCQiNBrOP9eagqdxKCBfXloUo0EnCGETxskuCbskcstDBuJLC9vf48VJX6dVj60JeHqrEeCoFHALGJgu/KHrnQwqShkAmGAFFV6uchpAFAVJH1VyGHEURssUC+LXsEQwuL67FAPISIqnJnAY2F3kRUkfVcyGkEEefzPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, ART = _t, #"15/01/2025" = _t, #"14/01/2025" = _t, #"13/01/2025" = _t, #"12/01/2025" = _t, #"11/01/2025" = _t, #"10/01/2025" = _t, #"09/01/2025" = _t, #"08/01/2025" = _t, #"07/01/2025" = _t, #"06/01/2025" = _t, #"05/01/2025" = _t, #"04/01/2025" = _t, #"03/01/2025" = _t, #"02/01/2025" = _t, #"01/01/2025" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type", "Source.Name", each if [Column1] = "Source.Name" then null else [Column1]),
#"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Source.Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column2","Source.Name"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom1", each if Text.Contains([Value], "/") then [Value] else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom", each let _SN = [Source.Name], _Attribute = [Attribute] in Table.SelectRows(#"Added Conditional Column",each [Source.Name] = _SN and [Attribute] = _Attribute and [Custom1] = null)[Value]),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom1] <> null)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Source.Name", "Attribute", "Value"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "ART", each "SUM"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"ART", "Custom1", "Custom"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Custom1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom1.1", "Custom1.2", "Custom1.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom1.1", Int64.Type}, {"Custom1.2", Int64.Type}, {"Custom1.3", Int64.Type}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"ART", "Custom1.2", "Custom1.1", "Custom1.3", "Custom"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Custom1.2", type text}, {"Custom1.1", type text}, {"Custom1.3", type text}}, "zh-CN"),{"Custom1.2", "Custom1.1", "Custom1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Custom", "Value"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Date", Order.Ascending}})
in
#"Sorted Rows"Original:
Result is as below.
Best Regards.
Rico Zhou
