Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am in the learning phase of the Power BI. I have been struggling on one problem for last three days. I have this below table.
I need to transform this into table looking like below.
Any help would be really appreciated.
Please just drop any tips if you don't think of any proper solution.
Thank You
Solved! Go to Solution.
@Anonymous
Paste this code in a New Blank Query in the Advanced Editor and check the steps. All using GUI.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcw7C4QwEATgv7JsbWOCsfbR2MhxbUgRNHAH9wARMfvrdQJJkWHYj4y1/PBb+O30DMt/W2kaKXLFw+v9WfNt9t9w30a/B6pzUbloOthVliHd/U6UiJC6QA/Al4gQVWAAaABCdAKVpxoAQpoCacoAEMoUSFMtACEtO3cB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Date 1", "Date 2", "Date 3 v"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Child Record Name"]), "Child Record Name", "Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Pivoted Column",{"A", "B", "C"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9"})
in
#"Split Column by Delimiter"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Paste this code in a New Blank Query in the Advanced Editor and check the steps. All using GUI.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcw7C4QwEATgv7JsbWOCsfbR2MhxbUgRNHAH9wARMfvrdQJJkWHYj4y1/PBb+O30DMt/W2kaKXLFw+v9WfNt9t9w30a/B6pzUbloOthVliHd/U6UiJC6QA/Al4gQVWAAaABCdAKVpxoAQpoCacoAEMoUSFMtACEtO3cB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Date 1", "Date 2", "Date 3 v"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Child Record Name"]), "Child Record Name", "Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Pivoted Column",{"A", "B", "C"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9"})
in
#"Split Column by Delimiter"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
You made my day. Thank you so much.
Is there any way where we can retain the column name?
@Anonymous
Please find below the code with the Column Names renamed as desired:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"PYw7CoAwEESvEra2SUSt/TQ2IrYhRTABBT8gFrqn11GSYh/DPHa0pt4efjvF4Mf9cKJtKKF6mhcXms6u/u0ae3ohQ1AhpGQSTejL9y6EG2AZRQWBhxtgFUUNkUIA/E+pMJVBAJxF8U3lEIDKo/imCgiACzLmAQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
#"(blank)" = _t,
#"(blank).1" = _t,
#"(blank).2" = _t,
#"(blank).3" = _t,
#"(blank).4" = _t
]
),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Merged Columns" = Table.CombineColumns(
#"Promoted Headers",
{"Date 1", "Date 2", "Date 3"},
Combiner.CombineTextByDelimiter("|", QuoteStyle.None),
"Merged"
),
#"Pivoted Column" = Table.Pivot(
#"Merged Columns",
List.Distinct(#"Merged Columns"[#"Child Record Name"]),
"Child Record Name",
"Merged"
),
#"Merged Columns1" = Table.CombineColumns(
#"Pivoted Column",
{"A", "B", "C"},
Combiner.CombineTextByDelimiter("|", QuoteStyle.None),
"Merged"
),
Step1 = Table.SplitColumn(
#"Merged Columns1",
"Merged",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{
"Merged.1",
"Merged.2",
"Merged.3",
"Merged.4",
"Merged.5",
"Merged.6",
"Merged.7",
"Merged.8",
"Merged.9"
}
),
Custom2 = Table.ExpandListColumn(
Table.AddColumn(
Table.FromList(
List.Distinct(#"Promoted Headers"[Child Record Name]),
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
"Custom",
each List.Skip(Table.ColumnNames(#"Promoted Headers"), 2)
),
"Custom"
),
NewHeaderList = Table.AddColumn(Custom2, "NewHeader", each [Column1] & " (" & [Custom] & ")")[
NewHeader
],
OldHeaderList = List.Skip(Table.ColumnNames(Step1), 1),
Step3 = Table.ToColumns(
Table.Transpose(Table.FromColumns({OldHeaderList, NewHeaderList}, {"Old", "New"}))
),
#"Renamed Columns" = Table.RenameColumns(Step1, Step3)
in
#"Renamed Columns"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
I will see that when I have time for sure.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |