Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I have an issue how to manage more dynamically columns heades in a table.
I have table 1 with current month, next month and next month +1. In a separate (table 2) I was able to select month I would like to appear.
MONTH
Aug 2022 |
Sep 2022 |
Oct 2022 |
I would like to have table 1 as the following
Table1.CurrentMonth = Aug 2022 Demand
Table1.NextMonth = Sep 2022 Supply
Table1.NextMonth1 = Oct 2022 Textxyz
Solved! Go to Solution.
Assume you only have the three months in the separate table 2, you can try my method shown as below.
Table2: add a date type column and sort it ascendingly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNVzAyMDJSitWJVgpOLUBw/JNLoJxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"MONTH", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "MONTH", "Month Start"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Month Start", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Month Start", Order.Ascending}})
in
#"Sorted Rows"
Table1: get sorted MONTH column from Table2, add corresponding suffix strings to each month value, and rename selected columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACRpiDSDMiOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, CurrentMonth = _t, NextMonth = _t, NextMonth1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"CurrentMonth", Int64.Type}, {"NextMonth", Int64.Type}, {"NextMonth1", Int64.Type}}),
#"Table2 Months" = Table2[MONTH],
#"New Column Headers" = List.Transform(List.Zip({#"Table2 Months", {"Demand", "Supply", "Textxyz"}}), each Text.Combine(_, " ")),
#"Rename Columns" = Table.RenameColumns(#"Changed Type", List.Zip({Table.ColumnNames(Table.SelectColumns(#"Changed Type", {"CurrentMonth", "NextMonth", "NextMonth1"})), #"New Column Headers"}))
in
#"Rename Columns"
Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Assume you only have the three months in the separate table 2, you can try my method shown as below.
Table2: add a date type column and sort it ascendingly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNVzAyMDJSitWJVgpOLUBw/JNLoJxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"MONTH", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "MONTH", "Month Start"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Month Start", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Month Start", Order.Ascending}})
in
#"Sorted Rows"
Table1: get sorted MONTH column from Table2, add corresponding suffix strings to each month value, and rename selected columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACRpiDSDMiOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, CurrentMonth = _t, NextMonth = _t, NextMonth1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"CurrentMonth", Int64.Type}, {"NextMonth", Int64.Type}, {"NextMonth1", Int64.Type}}),
#"Table2 Months" = Table2[MONTH],
#"New Column Headers" = List.Transform(List.Zip({#"Table2 Months", {"Demand", "Supply", "Textxyz"}}), each Text.Combine(_, " ")),
#"Rename Columns" = Table.RenameColumns(#"Changed Type", List.Zip({Table.ColumnNames(Table.SelectColumns(#"Changed Type", {"CurrentMonth", "NextMonth", "NextMonth1"})), #"New Column Headers"}))
in
#"Rename Columns"
Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.