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.
Hi Friends,
Anyone Please suggest me a idea in power query how to make a single title row by combine two rows title. Refer the below snapshot. In First two I have the Title in Excel sheet this type. Like first 3 column title contains in the Row1 and rest of the column main title are contains in Row2. So I want to make it in single row either merge or keep any one row as main title. Replace the Null Value in Row2 with Row1 value (or) Replace the Row1 Total as Row2 Value.
Please suggest a simplest and quick solution.
Thanks in Advance.
Solved! Go to Solution.
Hi @Nou_admin1 ,
Try this:
transpose table
merge first 2 columns
transpose table
promote header
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lEqMQIRxkqxOtFKQFYxiFsM4SaBZIDYDM4rAmJDIzi3CohNTMDcZCAzDYhN4bx0IIYoBbEygNgYhWcC54FMtVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Transposed Table" = Table.Transpose(Source),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
#"Promoted Headers"
@Nou_admin1 -Hello,From where you are taking the data if it is excel then may i see your column names in the excel sheets?
so that i can understand the column names properly.
@AntrikshSharma Thanks for your detailed reply. Is there any other simple solution ?
Hi @Nou_admin1 ,
Try this:
transpose table
merge first 2 columns
transpose table
promote header
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lEqMQIRxkqxOtFKQFYxiFsM4SaBZIDYDM4rAmJDIzi3CohNTMDcZCAzDYhN4bx0IIYoBbEygNgYhWcC54FMtVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Transposed Table" = Table.Transpose(Source),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
#"Promoted Headers"
@Nou_admin1 Yes, convert your data into an Excel table and make sure column headers are already correct in the original data.
@AntrikshSharmaThat I did already and used. Like Replace the Value by type the Value manually for the First 3 Rows...
Anyway Thanks for your detail source and It may helpful in any other datasets.
@Nou_admin1 Try this:
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45Wcs7PK85Mz0tNVdJRCijKTylNLol3zk9B4gJZIfkliTk46VidaCUgB4KCHX1cgZRbkCuIcs7JL87MS1cILslPzgbJgmjdzDzdkKJEoL0lYL0uiSWJQDliqdhYAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [
#"Corporate OFFICE RAIPUR" = _t,
Column2 = _t,
Column3 = _t,
Column4 = _t,
Column5 = _t,
Column6 = _t,
Column7 = _t
]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{
{ "Corporate OFFICE RAIPUR", type text },
{ "Column2", type text },
{ "Column3", type text },
{ "Column4", type text },
{ "Column5", type text },
{ "Column6", type text },
{ "Column7", type text }
}
),
FirstPart =
List.RemoveItems (
Table.ToRows (
Table.Range ( ChangedType, 0, 1 )
){0},
{ "Total" }
),
SecondPart =
List.RemoveItems (
Table.ToRows (
Table.Range ( ChangedType, 1, 1 )
){0},
{ "" }
),
NewColumnNames = FirstPart & SecondPart,
OldColumnNames = Table.ColumnNames ( ChangedType ),
RemovedTopRows = Table.Skip ( ChangedType, 2 ),
Result =
Table.RenameColumns (
RemovedTopRows,
List.Zip ( { OldColumnNames, NewColumnNames } )
)
in
Result
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.